I have posted some of the User Defined Functions to get
1.only Numeric,
2.Alpha
3.AlphaNumeric values from the String
Function to get Only Numeric Values from the string
1.only Numeric,
2.Alpha
3.AlphaNumeric values from the String
Function to get Only Numeric Values from the string
CREATE FUNCTION dbo.NumericOnly( @string VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @intAlpha INT SET @intAlpha = PATINDEX('%[^0-9]%', @string) WHILE @intAlpha > 0 BEGIN SET @string = STUFF(@string, @intAlpha, 1, '' ) SET @intAlpha = PATINDEX('%[^0-9]%', @string) END RETURN @string END GO SELECT dbo.NumericOnly('abcd!@#ABCD#$%123%^%^') Output : 123
Function to get Only Alpha Values from the string
CREATE FUNCTION dbo.AlphaOnly( @string VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @intAlpha INT SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @string) WHILE @intAlpha > 0 BEGIN SET @string = STUFF(@string, @intAlpha, 1, '' ) SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @string) END RETURN @string END GO SELECT dbo.AlphaOnly('abcd!@#ABCD#$%123%^%^') Output : abcdABCD
Function to get Only AlphaNumeric Values from the string
CREATE FUNCTION dbo.AlphaNumericOnly( @string VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @intAlpha INT SET @intAlpha = PATINDEX('%[^a-zA-Z0-9]%', @string) WHILE @intAlpha > 0 BEGIN SET @string = STUFF(@string, @intAlpha, 1, '' ) SET @intAlpha = PATINDEX('%[^a-zA-Z0-9]%', @string) END RETURN @string END GO SELECT dbo.AlphaNumericOnly('abcd!@#ABCD#$%123%^%^') Output : abcdABCD123