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.
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 (https://blog.sqlauthority.com)
86 Comments. Leave new
Very nice function. It worked 100%. thanks.
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?
Yes, the only thing you should change in place of :
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @string) is
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string).
By changing to SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string), you can get only numeric values.
This works great. Thanks.
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.
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
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.
still i have to test it in my server.
Can you tell me why you use “SET @string = @string”?
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
hi sheeba and all others who get this problem BY Using this Function .
ALTER function dblmatrix(@Column varchar(30)) returns varchar(50)
as
Begin
Declare @findex int
Declare @lfindex int
Declare @Beginse varchar(50)
Declare @lastse varchar(50)
Declare @lasts int
Declare @BeginCount int
declare @lngtrema int
set @findex = patindex(‘%-%’,@Column)
set @Beginse = substring(@Column,@findex+1,len(@Column))
set @lfindex = patindex(‘%-%’,@Beginse)
set @Beginse = substring(@Beginse,1,@lfindex-1)
return @Beginse
End
select dbo.dblmatrix(alpha) from sheeba
Data like this…
1234-sheeba-2345
1001234567-Projectname-(1234567)
Out put data like this
sheeba
Projectname
hi sheeba and all others who get this problem BY Using this Function We get solve this .
ALTER function dblmatrix(@Column varchar(30)) returns varchar(50)
as
Begin
Declare @findex int
Declare @lfindex int
Declare @Beginse varchar(50)
Declare @lastse varchar(50)
Declare @lasts int
Declare @BeginCount int
declare @lngtrema int
set @findex = patindex(‘%-%’,@Column)
set @Beginse = substring(@Column,@findex+1,len(@Column))
set @lfindex = patindex(‘%-%’,@Beginse)
set @Beginse = substring(@Beginse,1,@lfindex-1)
return @Beginse
End
select dbo.dblmatrix(alpha) from sheeba
Data like this…
1234-sheeba-2345
1001234567-Projectname-(1234567)
Out put data like this
sheeba
Projectname
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
@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.
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
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?
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?
Hi, thanks for the code. I’ve been looking for something like this for awhile.
Could you tell me one thing, though?
Why is it that if I leave the line:
SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
The query runs and cleans the non-alphanumeric characters out quickly.
But, if I want to add a space instead with:
SET @string = STUFF(@string, @IncorrectCharLoc, 1, ‘ ‘)
The query takes a long time to run. It seems that adding the space really negatively affects performance.
To aLL,
I have one table TABLE! , which contains one column named “EMPLOYEE NAME”.
There are various values inside it.
i want to check every field of EMPLOYEE NAME and find out if there is any Numeric Value inside.
EG.
EMPLOYEE NAME
Billgates
George
A1dam –Here 1 is present in between
Gem9ini –Here 9 is present in between
i want to identify this types of name which contains the numeric value in the field.
@Gates
WITH
EG([Employee Name])
AS
(
SELECT ‘Billgates’ UNION ALL
SELECT ‘George’ UNION ALL
SELECT ‘A1dam’ UNION ALL
SELECT ‘Gem9ini’
)
SELECT
[Employee Name]
FROM
EG
WHERE
[Employee Name] LIKE ‘%[0-9]%’
Thanx Pinal.
And now that i changed my DNS records, i am receiving email from your website. Silly bug in Exchange with CNAMEs.
Hey,
I have a column called runtime where it has the runtime of movies, its a TEXT field, an most of the values inside are in this format:
124m or 1546mins or 92 mins
so how can i strip only the numbers from there??
i really need ur help and thx alot in advance
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string)
END