SQL SERVER - UDF - Function to Parse AlphaNumeric Characters from String

Feed on: Posts; Comments

SQL SERVER - UDF - Function to Parse AlphaNumeric Characters from String

May 13, 2007 by pinaldave

Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is very handy function when working with Alphanumeric String only. I have used this many times. I actually have received this from previous Sr. DBA at my company.

CREATE FUNCTION dbo.UDF_ParseAlphaChars
    (
        @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.UDF_ParseAlphaChars('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
GO

Result Set : ABCID4e5F6

Reference : Pinal Dave (http://www.SQLAuthority.com), Previous DBA at my work.

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 15 Comments

15 Responses to “SQL SERVER - UDF - Function to Parse AlphaNumeric Characters from String”

on June 29, 2007 at 10:40 am Emanuel Costa

Very nice function. It worked 100%. thanks.
on July 7, 2007 at 10:26 am SQL SERVER - Convert Text to Numbers (Integer) - CAST and CONVERT Journey to SQL Authority with Pinal Dave

[...] How to parse/retrieve only numbers from column values contains alpha-numeric characters? SQL SERVER - UDF - Function to Parse AlphaNumeric Characters from String [...]
on September 4, 2007 at 10:45 am Ragz

Hi,
I am Newbie to sql. I found this function interesting. I have something that needs similar functionality but I am unable to apply this function to my table.

I have a table db2_coils where I have a column num_coil (this has both alphanumeric values Ex. B55436P) but what I need is just the numeric part of the num_coil(Ex. 55436) in a seperate column bse_num. Can I do that using above UDF, if yes How?
on September 27, 2007 at 7:19 am Debbie Alexander

This works great. Thanks.
on November 10, 2007 at 11:48 pm George Tzirtzi

Hi,

I am also newbie to sql. I would like to use this function to
filter user data in some forms, so that I avoid SQL injections.

However, I need to preserve the spaces in the data entry.
Can someone help me how to preserve spaces in the output
string???

P.S. Pinal Dave Thanks for this is great function.
on November 10, 2007 at 11:54 pm George Tzirtzi

Hi Guys and Girls,

to get just the numeric part replace the WHILE BEGIN END with the following:

WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
SET @IncorrectCharLoc = PATINDEX(’%[^0-9]%’, @string)
END
on December 18, 2007 at 9:41 pm Bryan Carter

Howdy Guys & Gals,

The above function to return a number as a string works. Though, if you want a number, even if there are no actual numbers in the provided string, then include this before the final line “RETURN @string”

IF(@string=”)
SET @string = ‘0′

And, for those using SQL Server 2005, you might want to use RegEx instead; muhch faster.
on March 7, 2008 at 4:45 pm Peacon

still i have to test it in my server.
on March 14, 2008 at 1:18 am Arsu

Can you tell me why you use “SET @string = @string”?
on August 1, 2008 at 11:22 am Sheeba

Hi

can you help me with a function to get only the alphabets from a field.

for eg: my field contains: 1001234567-Projectname-(1234567)

i would need only the “Projectname” from this

thanks
on September 26, 2008 at 7:45 pm M.Ganesan

DECLARE @intFlag INT
declare @string varchar(100)
set @string=’1001234567-Projectname-(1234567)’

declare @lsString varchar(100)
set @lsString=”
SET @intFlag = 1
WHILE (@intFlag <=len(@string))
BEGIN
if PATINDEX(’%[^0-9]%’, substring(@string,@intFlag,1))=1
and substring(@string,@intFlag,1)!=’-’
and substring(@string,@intFlag,1)!=’(’
and substring(@string,@intFlag,1)!=’)’
begin
set @lsString=@lsString+convert(varchar(100),substring(@string,@intFlag,1),105)
end

SET @intFlag = @intFlag + 1
END
PRINT @lsString
GO

Using this SQL u can get ONLY “Projectname”

with Regards
Ganesan.M
on September 27, 2008 at 6:39 am Imran Mohammed

@Ganesan

declare @cmd varchar(100)
set @cmd = ‘1001234567-Projectname-(1234567)’

select substring ( @cmd , charindex( ‘-’, @cmd)+1, (charindex( ‘(’, @cmd) - charindex( ‘-’, @cmd)+1)-3)

Result:
Projectname

This will work fine untill you have - before alphabets and -( at the end of the alphabets

Thanks,
Imran.
on November 15, 2008 at 2:05 pm sherin

Hi I hav a doubt …Hope you will help me.

I hav a column property with fld P_Id (varchar) , P_Name .

I want to select the Maximum Value of P_Id.if last

P_Id is ‘P41′ then next should be ‘P42′.How can i get the maximun of 41.
I tried
SELECT max(SUBSTRING(P_Id, 2, LEN(P_Id)))AS P_Id
FROM tblUser.
but retrns 9 because of varchar…
wht shld i doooooo.please help me
on November 21, 2008 at 10:21 am karol

Hi Dave,
When I tried to convert varchar column value to int from a table, one of value length more than 12 chars, then i got error message:
The conversion of the varchar value ‘133261280299′ overflowed an int column. Maximum integer value exceeded.

Can you please help?
on January 2, 2009 at 6:19 pm sombat thavorn

I would like to change my phone number into a numeric one. How can I do that?

I have gone through the reading, but do not understand what it is all about. would you please advise me how to do it?