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,/?6’)