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
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.
Actually lori ur Problem can be solved in Number of ways and one of the Solution is this ………
alter function splitdata(@Column varchar(30)) returns varchar(30)
as
Begin
Declare @Sfindex int
Declare @Beginse varchar(35)
Declare @lastse varchar(35)
Declare @lasts int
Declare @BeginCount int
declare @finalse varchar
set @Sfindex = patindex(‘% %’,@Column)
if @Sfindex !=0
Begin
set @Beginse = substring(@Column,1,@Sfindex-1)
End
else
set @Sfindex = patindex(‘%P%’,@Column)
if @Sfindex !=0
Begin
set @Beginse = substring(@Column,1,@Sfindex-1)
End
ELSE
set @Sfindex = patindex(‘%B%’,@Column)
if @Sfindex !=0
Begin
set @Beginse = substring(@Column,1,@Sfindex-1)
End
return @Beginse
End
alter function splitdataremain(@Column varchar(30)) returns varchar(30)
as
Begin
Declare @Sfindex int
Declare @Beginse varchar(35)
Declare @lastse varchar(35)
Declare @lasts int
Declare @BeginCount int
declare @finalse varchar
set @Sfindex = patindex(‘% %’,@Column)
if @Sfindex !=0
Begin
set @Beginse = replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
End
else
set @Sfindex = patindex(‘%P%’,@Column)
if @Sfindex !=0
Begin
set @Beginse =replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
End
ELSE
set @Sfindex = patindex(‘%B%’,@Column)
if @Sfindex !=0
Begin
set @Beginse = replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
End
return @Beginse
End
Data like this
287PHY
10102 PHY
10106 PHY
10106 BIO
32001 BIO
33501 BIO
4568PHY
10102 PHY
select dbo.splitdata(Subject)as credits from lori
select dbo.splitdataremain(Subject)as subject from Lori
Points subject
287 PHY
10102 PHY
10106 PHY
10106 BIO
32001 BIO
33501 BIO
4568 PHY
10102 PHY
Hi Lori and who gets this problem
alter function splitdata(@Column varchar(30)) returns varchar(30)
as
Begin
Declare @Sfindex int
Declare @Beginse varchar(35)
Declare @lastse varchar(35)
Declare @lasts int
Declare @BeginCount int
declare @finalse varchar
set @Sfindex = patindex(‘% %’,@Column)
if @Sfindex !=0
Begin
set @Beginse = substring(@Column,1,@Sfindex-1)
End
else
set @Sfindex = patindex(‘%P%’,@Column)
if @Sfindex !=0
Begin
set @Beginse = substring(@Column,1,@Sfindex-1)
End
ELSE
set @Sfindex = patindex(‘%B%’,@Column)
if @Sfindex !=0
Begin
set @Beginse = substring(@Column,1,@Sfindex-1)
End
return @Beginse
End
alter function splitdataremain(@Column varchar(30)) returns varchar(30)
as
Begin
Declare @Sfindex int
Declare @Beginse varchar(35)
Declare @lastse varchar(35)
Declare @lasts int
Declare @BeginCount int
declare @finalse varchar
set @Sfindex = patindex(‘% %’,@Column)
if @Sfindex !=0
Begin
set @Beginse = replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
End
else
set @Sfindex = patindex(‘%P%’,@Column)
if @Sfindex !=0
Begin
set @Beginse =replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
End
ELSE
set @Sfindex = patindex(‘%B%’,@Column)
if @Sfindex !=0
Begin
set @Beginse = replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
End
return @Beginse
End
Data like this
287PHY
10102 PHY
10106 PHY
10106 BIO
32001 BIO
33501 BIO
4568PHY
10102 PHY
select dbo.splitdata(Subject)as credits from lori
select dbo.splitdataremain(Subject)as subject from Lori
Points subject
287 PHY
10102 PHY
10106 PHY
10106 BIO
32001 BIO
33501 BIO
4568 PHY
10102 PHY
@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);
Brian Tkatch,
do have any different approach or solution ?
your sample isnot working properly…
Thanks..
There are two problems
1 Single quotes are represented differently in this site
2 The above suggestion will not work versions prior to 2005
@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
hi enrico And who get this problem. I think we can more easily get solve this problem…….. by using this Function….
ALTER function misInvdata(@Column varchar(30)) returns varchar(30)
as
Begin
Declare @Count int
declare @afterCount int
Declare @Sfindex int
Declare @Beginse varchar(35)
Declare @lastse varchar(35)
Declare @lasts int
declare @resulindex int
Declare @BeginCount int
declare @finalse varchar
declare @storecolumn varchar(100)
if @Column !=’Inv005′
Begin
set @Sfindex = patindex(‘%v%’,@column)
set @Beginse = substring(@Column,1,@Sfindex)
set @lastse = substring(@Column,@Sfindex+1,len(@Column))
set @lasts = cast(@lastse as int)
set @lasts =@lasts + 1
set @lastse =cast(@lasts as varchar(25))
while(len(@lastse)<3)
Begin
set @lastse ='0'+ @lastse
End
set @Beginse =@Beginse + @lastse
End
return @Beginse
End
and by this query…..
select dbo.misInvdata(Invdata) from Invdata where dbo.misInvdata(Invdata) not In (Select Invdata from Invdata)
DATA LIKE THIS …….
Inv001
Inv002
Inv004
Inv005
OUTPUT:Inv003
nOte:the Last row should be omitted in the Function thats hardcoded here……… or You can do this (omit last row) in query too in dbo.misInvdata(Invdata).
@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
I have seen the Posted things for this Problem No body has given a perfec’yet’ Out put than this.But please do not Copy it nor Mar it…This is Called Selective-Insertion algorithm for Missing Numbers By Bhushan works in all Versions………..tested…
Declare @maxnum int
Declare @sindex int
declare @disprec varchar(500)
Declare @Beginse varchar(500)
Declare @originalstr varchar(8000)
Declare @originaldisp varchar(8000)
Declare @sdispindex int
Declare @strlen int
Declare @searchd int
Declare @searchst varchar(30)
Declare @origlen int
Declare @concatinaldisp varchar(30)
Declare @topminrumdum int
select @maxnum = max(cast(Right(Mxvar,4)as int)) from Maxvar
select Right(Mxvar,4)+ 1 as Mxvar into #temptested from Maxvar
select @disprec = cast(Mxvar as varchar(45)) from #temptested
select @Beginse = Mxvar from Maxvar
select @disprec = @disprec + ‘-’ + cast(Mxvar as varchar(45)) from #temptested
select @Beginse =@Beginse+’-’ + Right(Mxvar,4) from Maxvar
set @sdispindex = patindex(‘-’,@disprec)
set @originaldisp = substring(@disprec,@sdispindex+1,len(@disprec))
set @sindex =patindex(‘%-%’,@Beginse)
if @sindex > 0
Begin
set @originalstr = substring(@Beginse,@sindex+1,len(@Beginse))
End
set @origlen=patindex(‘%-%’,@originaldisp)
set @originaldisp = substring(@originaldisp,@origlen+1,len(@originaldisp))
set @strlen =len(@originaldisp)
while(@strlen >0)
Begin
set @searchd = patindex(‘%-%’,@originaldisp)
if @searchd = 0
Begin
set @originaldisp=”
update #temptested set Mxvar=Mxvar+1 where Mxvar=Mxvar
select @topminrumdum = min(Mxvar) from #temptested
select @maxnum =max(cast(Right(Mxvar,4)as int)) from Maxvar
select @disprec = cast(Mxvar as varchar(45)) from #temptested
select @disprec = @disprec + ‘-’ + cast(Mxvar as varchar(45)) from #temptested
set @sdispindex = patindex(‘-’,@disprec)
set @originaldisp = substring(@disprec,@sdispindex+1,len(@disprec))
set @origlen=patindex(‘%-%’,@originaldisp)
set @originaldisp = substring(@originaldisp,@origlen+1,len(@originaldisp))
set @searchd = patindex(‘%-%’,@originaldisp)
if(@maxnum @topminrumdum or @maxnum != @topminrumdum)
Begin
set @concatinaldisp =substring(@originaldisp,1,@searchd)
set @concatinaldisp = replace(@concatinaldisp,’-‘,”)
while(len(@concatinaldisp) @maxnum)
Begin
set @sindex = 1
End
Else
set @sindex = charindex(@concatinaldisp,@originalstr)
if @sindex =0
Begin
set @originalstr = @originalstr + ‘-’ +@concatinaldisp
insert into #temper(mxvar)values(@concatinaldisp)
–print @concatinaldisp
–print @originalstr
End
End
End
End
@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
Hi
I am facing a problem while using the ISNUMERIC fucntion.
I have a huge table. One of the columns is Global_Product_id. now this is in varchar. I have created a backup of this same table with similar structure except that the column Global_ProductId is now Bigint
Now using the query below to insert value in that column
SELECT CASE WHEN ISNUMERIC(LTRIM(RTRIM(GLOBAL_PRODUCT_ID)))=1 THEN CAST(LTRIM(RTRIM(GLOBAL_PRODUCT_ID)) AS BIGINT)
ELSE NULL END GLOBAL_PRODUCT_ID
FROM
gives me the error – error converting varchar to numeric.
It seems ISNUMERIC is unable to detect the field properly. There may be some ascii chachetres in the columns. iT was loaded form flat file.
Can you help me in resolving this issue?
Hello ,
I have a column having nvarchar datatype which contain values like = 1,2,3,4..,100,101..,1010…, MCA1, MCA2,…,MBA1, MBA2…. etc like this. I want to retrieve only all integer data just like (1,2,3,4,..,100,101..,1010..) Not all data.. how to retrieve it.. I am using Substring(MCA10 , patindex(‘%[0-9]%’,MCA10),len(MCA10)) to retrieve Numeric part of alphanumeric number. But i want only integer value (1,2,3,4,….) not alphanumeric value.
Quite a few posters have asked for a function that just returns the numbers from a string.
This is an adaptation of Pinal’s function that will do return a string containing only the characters 0-9 from the input string.
CREATE FUNCTION dbo.fnctParseNumericChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
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
GO
@Prashant Kumar
Use WHERE ISNUMERIC(col) = 1
or
where col not like ‘%[^0-9]%’
Awesome site helpful for complete beginners.
@Brian Tkatch,
You might want to reconsider… ISNUMERIC should never be used as ISALLDIGITS… it wasn’t designed for that.
SELECT ISNUMERIC(‘2e3’),
ISNUMERIC(‘2d3’),
ISNUMERIC(‘1,000’),
ISNUMERIC(CHAR(9)),
ISNUMERIC(‘$’),
ISNUMERIC(‘-‘)
@Jeff
You are correct. I forget that sometimes and try to avoid using that FUNCTION myself.
Thank you for the reminder.
Mattering only where millions of rows are present, the following modification of Pinal’s good code is about 11 seconds faster on a million rows on a 7 year old single 1.8Ghz CPU…
CREATE FUNCTION dbo.AlphaNumeric
— Modified by Jeff Moden
(@String VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @String)
WHILE @IncorrectCharLoc > 0
SELECT @string = STUFF(@String, @IncorrectCharLoc, 1, ”),
@IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @String)
RETURN @string
END