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 (http://blog.SQLAuthority.com)
pinaldave
Very nice function. It worked 100%. thanks.
[...] How to parse/retrieve only numbers from column values contains alpha-numeric characters? SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String [...]
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?
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
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.
[...] SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String [...]
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]%’
Brian,
Thanks very good solution.
Regards,
Pinal
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
how can i get all numeric values as numeric value in a table ?
i mean, the result set should be in a table format not scalar varchar value.
your sample statement:
SELECT dbo.UDF_ParseAlphaChars(‘ABC”_I+{D[]}4|:e;””5,/?6′)
result should be:
Number
4
5
6
Thanks
I need to parse out the first set up numbers in a column before the first character in a string:
example
287PHY 10102 PHY 10106 BIO 32001 BIO 33501
4568PHY 10102 PHY 10106 BIO 32001 BIO 33501
32891PHY 10102 PHY 10106 BIO 32001 BIO 33501
I need to parse out the
287
4568
32891
in a separate column.
Can someone help me out?
Thanks in advance.
@Adam
Here is a quick CTE approach to putting each as a separate record.
WITH
Data(String) AS (SELECT ‘ABC”_I+{D[]}4|:e;”"5,/?6′),
CTE
AS
(
SELECT
0 X_Current,
LEN(String) X_Max,
” Digit
FROM
Data
UNION ALL
SELECT
CTE.X_Current + 1,
CTE.X_Max,
SUBSTRING(Data.String, CTE.X_Current, 1)
FROM
CTE,
Data
WHERE
CTE.X_Current <= CTE.X_Max
)
SELECT
Digit
FROM
CTE
WHERE
Digit LIKE '[0-9]'
OPTION
(MAXRECURSION 0);
@Lori
WITH
Data(String)
AS
(
SELECT ‘287PHY 10102 PHY 10106 BIO 32001 BIO 33501′ UNION ALL
SELECT ‘4568PHY 10102 PHY 10106 BIO 32001 BIO 33501′ UNION ALL
SELECT ‘32891PHY 10102 PHY 10106 BIO 32001 BIO 33501′
)
SELECT
SUBSTRING
(
String,
1,
PATINDEX(‘%[^0-9]%’, String) – 1
)
FROM
Data;
Hi Pinal/Folks,
Can anyone please help me? I am stunned at this one.
Let’s say I have the following records:
INV0096
INV0097
INV0099
INV0100
INV0102
INV0103
How can I generate a SQL Script that will show me the gaps in the sequence?
In such a way that the results will give me INV0098 and INV0101.
Or even just the number 98, and 101.
Thank you so much for your time.
Best regards,
Enrico
@enrico
WITH
Data(Datum)
AS
(
SELECT ‘INV0096′ UNION ALL
SELECT ‘INV0097′ UNION ALL
SELECT ‘INV0099′ UNION ALL
SELECT ‘INV0100′ UNION ALL
SELECT ‘INV0102′ UNION ALL
SELECT ‘INV0103′
),
CTE
AS
(
SELECT
CAST(SUBSTRING(MIN(Datum), 4, 4) AS INT) Start,
CAST(SUBSTRING(MAX(Datum), 4, 4) AS INT) Finish
FROM
Data
UNION ALL
SELECT
Start + 1,
Finish
FROM
CTE
WHERE
Start < Finish
)
SELECT
Common.Formatted
FROM
CTE
CROSS APPLY(SELECT 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)) Common(Formatted)
WHERE
NOT EXISTS
(
SELECT
*
FROM
Data
WHERE
Data.Datum = Common.Formatted
)
OPTION
(MAXRECURSION 0);
The CROSS APPLY is just nice, but not required. Without it, the query is very similar:
WITH
Data(Datum)
AS
(
SELECT 'INV0096' UNION ALL
SELECT 'INV0097' UNION ALL
SELECT 'INV0099' UNION ALL
SELECT 'INV0100' UNION ALL
SELECT 'INV0102' UNION ALL
SELECT 'INV0103'
),
CTE
AS
(
SELECT
CAST(SUBSTRING(MIN(Datum), 4, 4) AS INT) Start,
CAST(SUBSTRING(MAX(Datum), 4, 4) AS INT) Finish
FROM
Data
UNION ALL
SELECT
Start + 1,
Finish
FROM
CTE
WHERE
Start < Finish
)
SELECT
'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)
FROM
CTE
WHERE
NOT EXISTS
(
SELECT
*
FROM
Data
WHERE
Data.Datum = 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)
)
OPTION
(MAXRECURSION 0);
@enrico,
You can find Missing values with Recursive CTE as follwing, It will work only with SQL 2005 and above.
DECLARE @Test TABLE (Data VARCHAR(10))
INSERT INTO @test
SELECT ‘INV0096′
UNION ALL
SELECT ‘INV0097′
UNION ALL
SELECT ‘INV0099′
UNION ALL
SELECT ‘INV0100′
UNION ALL
SELECT ‘INV0102′
UNION ALL
SELECT ‘INV0103′
UNION ALL
SELECT ‘INV0106′
UNION ALL
SELECT ‘INV0110′
;with cte1 as(
SELECT CAST(RIGHT(Data,4) AS INT) As RowID
FROM @Test
), Missing as(
SELECT MIN(RowID) AS MissNum,
MAX(RowID) AS MaxID
FROM Cte1
UNION ALL
SELECT MissNum + 1,
MaxID
FROM Missing
WHERE MissNum < MaxID
)
SELECT missnum
FROM Missing
LEFT JOIN cte1 tt on tt.Rowid = Missing.MissNum
WHERE tt.Rowid is NULL
OPTION (MAXRECURSION 0);
Let us know if it helps you.
Thanks,
Tejas
(SQLYoga.com)
Hi Brian/Tejas,
Thanks for that!
That worked beautiful on SQL2005! You guys are awesome.
On the off chance, is there a way to get the same result on SQL2000?
I definitely appreciate your time guys. I can’t even begin to express my thanks for helping me out on this.
I’ll look forward to your thoughts on the same issue for SQL2000.
Thanks again.
Regards,
enrico
@Enrico,
I developed this script for SQL 2000.
In this script, there is limitation, that it will give you only one missing value.
If we miss 104,105 (means more than one value we missed) then it will return only one.
DECLARE @Test TABLE (Data VARCHAR(10))
INSERT INTO @test
SELECT ‘INV0096′
UNION ALL
SELECT ‘INV0097′
UNION ALL
SELECT ‘INV0099′
UNION ALL
SELECT ‘INV0100′
UNION ALL
SELECT ‘INV0102′
UNION ALL
SELECT ‘INV0103′
UNION ALL
SELECT ‘INV0106′
UNION ALL
SELECT ‘INV0110′
CREATE TABLE #test (ID INT IDENTITY, RowID INT)
INSERT INTO #test(RowID)
SELECT CAST(RIGHT(Data,4) AS INT) As RowID
FROM @Test
ORDER BY CAST(RIGHT(Data,4) AS INT)
SELECT t1.RowID + 1
FROM #test t1
INNER JOIN #test t2 ON t1.ID + 1 = t2.ID
AND t1.RowID + 1 t2.RowID
DROP TABLE #test
I will try to make it as I made for SQL 2005, but I think it will take time.
Let me know if it helps you.
Thanks,
Tejas
(SQLYOGA.com)
Hi Tejas,
Thanks for that mate!
I really appreciate the assistance!
Thanks Pinal for putting up such an awesome site!!
Regards,
enrico
@enrico
i just write this and good to see that it also working in SQL 2000 hope will be helpful to you,,,
DECLARE @Test TABLE (Data VARCHAR(10))
INSERT INTO @test
SELECT ‘INV0096′
UNION ALL
SELECT ‘INV0097′
UNION ALL
SELECT ‘INV0099′
UNION ALL
SELECT ‘INV0100′
UNION ALL
SELECT ‘INV0102′
UNION ALL
SELECT ‘INV0103′
UNION ALL
SELECT ‘INV0106′
UNION ALL
SELECT ‘INV0110′
select number from master..spt_values where
number not in (select right(data,4) from @test) and
number between (select min(right(data,4)) from @test)
and (select max(right(data,4)) from @test) and TYPE=’P’
Dear,
Tanks a lot! Perfect!
Actually you have a great knowledge, congratulations!
Its Very Good. Thank you