Tuesday, July 14, 2009
Split value function SQL SERVER
Friday, July 3, 2009
Fetch Numer/AlphaNumeric value from Varchar table's Field In SQL SERVER
Following function keeps only numeric characters in string and removes all the other
character from the string. This is very handy function.
Create FUNCTION dbo.GetNumberFromVarcharField
(
@string VARCHAR(max) -- varchar field value
)
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc,1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
END
SET @string = @string
RETURN @string
END
--- test
SELECT dbo.GetNumberFromVarcharField('sadas????ASDa######10')
Following function keeps only Alphanumeric characters in string and removes all
the other character from the string. This is very handy function too.
CREATE FUNCTION dbo.GetAlphaNumericString
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%',@string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc,1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
END
SET @string = @string
RETURN @string
END
GO
-- Test
SELECT dbo.GetAlphaNumericString('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
Wednesday, July 1, 2009
True Random and Unique AlphaNumeric Number Generator
Forums for Microsoft .NET developers
Hi begginers to Microsoft.NET world, here a list of forums and educational sites where you can post your queries and get ur answers from professionals. this may help you to increase your knowledge and also help if u stuck with some issues…become a regular member(its free) so that source code access is easy ….and also contibute wen u have enough greymatter to solve complex issues of .NET. Comment on this post and add more forums which i might have missed …..
www.w3schools.comTuesday, June 30, 2009
Find out the Binary, ASCII and Character of a Given String in SQL Server
When you are storing data inside fields like ‘address’, there are bound to be unusual characters in it which make way due to poor validation rules. A good way to look for them is to convert your string to varbinary.
Here’s the query:
DECLARE @MyAddress varchar(35) SET @MyAddress = 'CANTB RY EA%T P.O.Box 55343' DECLARE @BIN AS VARBINARY(100) SET @BIN = convert(varbinary(100),@MyAddress) SELECT SUBSTRING(@BIN, Number, 1) AS Binary, ASCII(SUBSTRING(@BIN, Number, 1)) AS ASCII, CHAR(ASCII(SUBSTRING(@BIN, Number, 1))) AS Character FROM master..spt_values WHERE Type = 'p' AND Number BETWEEN 1 AND DATALENGTH(@BIN)
Monday, June 29, 2009
Using JavaScript for Validation of Different Date Formats in Asp.NET Web Form
22 Visual Studio Short Keys and 6 Short-cut Ways to Custom Jobs: A List of Tips and Tric
Friday, June 26, 2009
How to detect browser using JavaScript
Thursday, June 25, 2009
Generating Unique Keys in .Net
Using DateTime and HashCode:
So then I tried
Guid.NewGuid().ToString().GetHashCode().ToString(”x”);
private string GetUniqueKey()
{
int maxSize = 8;
int minSize = 5;
char[] chars = new char[62];
string a;
a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890";
chars = a.ToCharArray();
int size = maxSize;
byte[] data = new byte[1];
RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();
crypto.GetNonZeroBytes(data);
size = maxSize;
data = new byte[size];
crypto.GetNonZeroBytes(data);
StringBuilder result = new StringBuilder(size);
foreach (byte b in data)
{
result.Append(chars[b % (chars.Length - 1)]);
}
return result.ToString();
}
Analysis shows that RNGCrypto with Character Masking is best method to generate Unique keys.
How to retrieve the identity value when inserting a record into a Microsoft SQL Server table
A common requirement when inserting a record into a table that contains an identity column is to return the identity value of the newly inserted record. A common mistake is to use @@IDENTITY or IDENT_CURRENT, both of which could return an unexpected value under some circumstances. This document describes a technique to use SCOPE_IDENTITY which does not suffer the drawbacks of the other two methods.
Why @@IDENTITY and IDENT_CURRENT should not be used
Using @@IDENTITY after an insert will return the last-inserted identity value. However, this might be an unexpected value if, for example, the inserted record fires triggers to perform additional inserts. In this case, calling @@IDENTITY immediately after the statement will return the last identity value generated by the triggers.
Using IDENT_CURRENT after an insert will return the last-inserted identity value for a specified table. However, this might be an unexpected value if another insert on the table is performed between the time of the initial insert and the calling of IDENT_CURRENT.
How and why to use SCOPE_IDENTITY
Using SCOPE_IDENTITY avoids the drawbacks of using @@IDENTITY and IDENT_CURRENT.Note however that SCOPE_IDENTITY is only available with Microsoft SQL Server 2000 or later.The technique for using SCOPE_IDENTITY is to call it immediately after the INSERT statement within a stored procedure. The following example shows a stored procedure called InsertCases that uses this technique to return the identity value for the CaseID identity column of the inserted record. This example has two columns, in addition to the CaseID identity column, called CaseName and CaseDescription. Notice that the @CaseName and @CaseDescription are input parameters but the @CaseID parameter is an output parameter used to return the identity value on exit from the stored procedure.
CREATE PROCEDURE [dbo].[InsertCases] @CaseID int output, @CaseName nvarchar(255), @CaseDescription ntext AS SET NOCOUNT ON INSERT INTO [dbo].[Cases] ([CaseName],[CaseDescription]) VALUES (@CaseName,@CaseDescription) SET @CaseID=SCOPE_IDENTITY() SET NOCOUNT OFF
The following example shows how to call the InsertCases stored procedure using the Microsoft .NET Framework. Notice that the @CaseID parameter is set as an output parameter by setting its Direction property to arameterDirection.Output which allows it to return the CaseID identity value after the stored procedure has been executed.
C#
Command.Parameters.Add(new System.Data.SqlClient.SqlParameter(”@CaseID”,System.Data.SqlDbType.Int));
Command.ExecuteNonQuery();
CaseID = System.Convert.ToInt32( Command.Parameters["@CaseID"].Value);
Conclusion
By using SCOPE_IDENTITY within a stored procedure, the identity value of the most recently inserted record can always be correctly obtained.