Split value function SQL SERVER

Create FUNCTION [dbo].[SplitValues](@Str varchar(max),@Deliminator varchar(100)) returns @t table (numberval numeric(25), stringval varchar(100), DateVal datetime) as begin declare @i int; declare @c varchar(100); --set @Str = @Str + ',' set @Str = @Str + @Deliminator set @i = 1; set @c = ''; while @i <= len(@Str) begin --if substring(@Str,@i,1) = ',' if substring(@Str,@i,1) = @Deliminator begin insert into @t values (CASE WHEN isnumeric(@c)=1 THEN @c else Null END, rtrim(ltrim(@c)), CASE WHEN isdate(@c)=1 then @c else Null END) set @c = '' end else set @c = @c + substring(@Str,@i,1) set @i = @i +1 end return end ---------------- to call this function,use following... select * from dbo.SplitValues('1,2,3,4,5',',') ------------- this will return following results... items -------- 1 2 3 4 5

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')

True Random and Unique AlphaNumeric Number Generator

public string GetRandomUniqueAlphaNumericCode(int length, bool lowerCase) { int maxSize = 8; 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)]); } strCode = (result.ToString()).ToUpper(); bool flag = false; for (int i = 0; i < strCode.Length; i++) { if (char.IsDigit(strCode, i)) { flag = true; } } if (flag == false) { GetRandomUniqueAlphaNumericCode(); } return strCode; } here strCode is static class variable. this will generate random and unique alphanumeric string.

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.eggheadcafe.com

www.c-sharpcorner.com

www.vbdotnetheaven.com

www.dotnetheaven.com

forums.asp.net

www.codeproject.com

www.eggheadcafe.com

www.4guysfromrolla.com

www.aspalliance.com

www.devasp.net

DotNetCommunity

www.w3schools.com