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

No comments:

Post a Comment

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...