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)
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
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.
[...] 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.
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
@P Sen
ISNUMERIC includes money types. Perhaps you can use a user defined FUNCTION. Pinal has one here: http://blog.sqlauthority.com/2007/08/11/sql-server-udf-validate-integer-function/
@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
As a side bar, I really hate what this forum does to nicely formatted code. ;-)
This is another method (needs little bit change)
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx
Madhivanan
hi ernico and all others who get this problem……
Use this
ALTER function misInvdata(@Column varchar(30)) returns varchar(30)
as
Begin
Use this function
Declare @Sfindex int
Declare @Beginse varchar(35)
Declare @lastse varchar(35)
Declare @lasts int
Declare @BeginCount int
declare @finalse varchar
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
return @Beginse
End
and Use this query
select dbo.misInvdata(Invdata) from Invdata where dbo.misInvdata(Invdata) not in( select invdata from invdata union select max(dbo.misInvdata(invdata)) from invdata)
DAta like this
Inv001
Inv002
Inv004
Inv005
Inv007
Output data like this
Inv003
Inv006
use this Function query No need to hard code any thing…
ALTER function misInvdata(@Column varchar(30)) returns varchar(30)
as
Begin
Declare @Sfindex int
Declare @Beginse varchar(35)
Declare @lastse varchar(35)
Declare @lasts int
declare @finalse varchar
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
return @Beginse
End
and Use this query
select dbo.misInvdata(Invdata) from Invdata where dbo.misInvdata(Invdata) not in( select invdata from invdata union select max(dbo.misInvdata(invdata)) from invdata)
Data like this
Inv001
Inv002
Inv004
Inv005
Inv007
Output data like this
Inv003
Inv006
Hi Enrico and all who get this Problem I have seen several posts(solutions) for this problem.But No post have been provided a Pefec’yet’ solution Better than this.I request You do not copy this or Nor mar it.just for analysis i am giving this.This is Called Increament-Selection-Itterative ‘Batch’ for Missing Sequence Number by Bhushan.kambampati.
Declare @minnum int
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 @minnum =min(Right(Mxvar,4)) 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
–create Table #temper
–(
–mxvar varchar(35)
–)
–drop table #temptested
–Delete from Maxvar where Mxvar=’Inv9999′
– insert into Maxvar(Mxvar)values(‘Inv1599′)
select distinct(Mxvar) from #temper order by Mxvar asc
Hi Enrico and all who get this Problem I have seen several posts(solutions) for this problem.But No post have been provided a Pefec’yet’ solution Better than this.I request You do not copy this or Nor mar it.just for analysis i am giving this.This is Called Increament-Selection-Itterative ‘Batch’ for Missing Sequence Number by Bhushan.kambampati.
Declare @minnum int
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 @minnum =min(Right(Mxvar,4)) 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
–create Table #temper
–(
–mxvar varchar(35)
–)
–drop table #temptested
–Delete from Maxvar where Mxvar=’Inv9999′
– insert into Maxvar(Mxvar)values(‘Inv1599′)
select distinct(Mxvar) from #temper order by Mxvar asc
The Algorithm is More very Much parllel to the Selection and insertion Sort and Merge sorts,Heap sorts of some other sorting Techniques.
Algorithm for Increament-selection and Itteration to Find the Missing Sequence Number.
Step:1 Let N-be the values or terms of the Tabular data.Make Each and Every value to N+1 values of the tabular data
Step:2 Check Each and Every value which is N+1 but Not in N-values.if Such values are found.Merge into N-values string Go to step4
step3:Update the Each values of N+1 terms increament by ’1′ go to step2
step4: check the Max value of the N-tabular data. And Updated value of the Minimum or first row of N+1 tabular data is Equal or Greater than Max (of n- term tabular data). if so Exit the Loop else go to step 3
Note: Omit the Last value or Last row in Each N+1 increament values when Your comparing the N-term strings And this algorithm only works if you know the Max value of the N-term.and Minimum row of the each updated by N+1 terms( increamented by 1).(In computation it is possible).
And there should be No restrictions for the Missing sequence(It may differ by 1 ,1000,1,00,000 e.t.c).
Advantages: You can work it what ever be the order of the sequence.They may be Increasing or Decreasing Order.still You find the Sequence Numbers….
Thanks ……..
Bhushan.kambampati…..
The Algorithm is More very Much parllel to the Bubble, Selection and insertion Sort and Merge sorts,Heap sorts or some other sorting Techniques.
Algorithm for Increament-selection – Itteration to Find the Missing Sequence Number.
Step:1 Let N-be the values or terms of the Tabular data.Make Each and Every value to N+1 values of the tabular data
Step:2 Check Each and Every value which is N+1 but Not in N-values.if Such values are found.Merge into N-values Go to step4
step3:Update the Each values of N+1 terms increament by ‘1′ go to step2
step4: check the Max value of the N-tabular data. And Updated value of the Minimum or first row of N+1 tabular data is Equal or Greater than Max (of n- term tabular data). if so Exit the Loop else go to step 3
Note: Omit the Last value or Last row in Each N+1 increament values when Your comparing the N-term tabular data.And this algorithm only works if you know the Max value of the N-term.and Minimum row value (or) first value of the each updated N+1 terms( increamented by 1).(In computation it is possible).
And there should be No restrictions for the Missing sequence(It may differ by 1 ,1000,1,00,000 e.t.c in each N-values of Tabular data).
Advantages: You can work it whatever be the order of the sequence Numbers.They may be Increasing or Decreasing Order.still You find the Missing Sequence Numbers….
Thanks….
Bhushan.kambampati…..
hi friends,
can any one help me pl!!
in sql 2005.i have created a table with a column name as rid,total,cur_value and balance.
In balance column, this total value minus(-) cur_value is stored.
1)now,after each transaction i wanna store this balance value into the total column again automatically…how to do it?
2)how to increment the rid value lik (1,2,3,4,5…)after each transaction is executed?
3) how to get a particular value in a table using aggregate as condition ?
1 Use computed column with your caluculation
2 Use identity column that will take care of what you want to do
3 Can you post some more informations?
Great Post and Comments !!!
I just thought, how will I extract only numeric. And wooo…. I got it from one of the comment (from George Tzirtzi).
And one of the commentor told that, “We can also use it to avaoid SQL injection”. I will try this too.
I read all your Top Post today. You have large amout of comments in it. I learned a lot from articles as well as comments by other SQL experts.
Hi ,
How to sort the alphanumeric value in the table column.
Post some sample data with expected output
Hi, From seeing the above post I got to know that from SQL u can parse the alpha numeric value. In my case, I have a field which has charecters like “SN23024942;2009-01-01;DMM3403A”. This field is a combination of a serial number, date and the model number of the parts of an inventory. I actually want to just remove the date part 2009-01-01 and store it in a field which is like a timestamp “2009-01-01 00:00″ so that I could track the parts by using date. Is this possible from any of the above functions?
Thanks in advance.
Try this way
declare @s varchar(30)
set @s=’SN23024942;2009-01-01;DMM3403A’
select @s as string, parsename(REPLACE(@s,’;',’.'),2) as date
Hi there,
I hope you guys can help me! I am so stuck!
I have a alphanumeric column call “DWG Rev”. Revisions start at A, B C etc. Once the drawings is accepted the revisions become numbers i.e 1, 2, 3.
I need to get the Max of revision where numbers are latest.
SO
DWG Number | Drawing Date | DWG Rev
Group by Last Max?
This is where I am having the problem, when using aggregate function I would like to get the latest drawing by selecting the last date however, I would like to show the revision but not sure how. Please help
I’m trying to sort on an odd alpha numeric value. I’ve tried LEN and SUBSTRING but I just can’t seem to find the correct combination. Here is the list of values that I get, followed by what I want to get.
DESC sort
170-151-180(1)(d)
170-151-180(10)
170-151-180(10)(a)
170-151-180(10)(b)
170-151-180(11)
170-151-180(2)
THIS IS WHAT I WOULD LIKE IT TO SORT ON.
170-151-180(1)(d)
170-151-180(2)
170-151-180(10)
170-151-180(10)(a)
170-151-180(10)(b)
170-151-180(11)
Any help please.
I was able to get the sort worked out. Here is my solution, but I’d be interested to see other solutions as well.
declare @r1 table (ANUM varchar(500),ORD varchar(500))
insert into @r1(ANUM ,ORD)
select ANUM ,replace(ANUM ,’ ‘,”) from refTBL where ((PATINDEX(‘%)%’, ANUM ))-(PATINDEX(‘%(%’, ANUM ))) != 2
union
SELECT ANUM ,replace(REPLACE(ANUM ,’(‘,’(0′),’ ‘,”) from refTBL where ((PATINDEX(‘%)%’, ANUM ))-(PATINDEX(‘%(%’, ANUM ))) = 2
SELECT ANUM from @r1
order by ORD
Very helpful function. Thanks for sharing
Suppose I Want To Add ‘i’ Word At The End Of ‘DELH’ How Can I Do IT?
I Am Using Following Function But Results Comes Null
Select Stuff(‘DELH’,5,0,’i')
Any help please?
Hi,
how to order on this condition :-
Name(varchar)(coloum name)
Ram
Mohan
1234 8876
Abhi
1234 8877
and i want to order like this :-
Name
Abhi
Mohan
Ram
1234 8876
1234 8877
Thanks
Data Base :-Sql server 2000 and 2005 both
Can you explain why you have these two lines?
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @string) — 2nd one
SET @string = @string
I created a version without them and it works fine. I don’t see the point of these lines, but if I am missing something I would like to know. Either way, it is a great solution. Thank you.
Hi ,
I am new to sql
Could some one help me with parsing function in SQL server in splitting the value form comma separated string.
The output of the column looks like…
Patient Othernumber
—————————–
123
567,56e3,785
746
66y,8968g,5656,4435
i wan to split this output.
Thank you.
What is your expected result?
Hi,
Thank you very much for your quick response. I have written a function to split these output and it works fine for me.