Following SQL User Defined Function will extract/parse numbers from the string.
CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END
RETURN @IntNumbers
END
GO
Run following script in query analyzer.
SELECT dbo.ExtractInteger('My 3rd Phone Number is 323-111-CALL')
GO
It will return following values.
3323111
Reference : Pinal Dave (https://blog.sqlauthority.com)
39 Comments. Leave new
can you please help me how to extract date and time from string? here is my example:
person A at 2006 345 15:24 See attachment from Ron Holden person B at 2006 361 16:20 Refer to AR 113831.
i would like output will be like : 2006 345 15:24
I ready appreciate for all your helped and supported. I looking forward to hear from you soon.
Big Thanks,
Tammy
declare @s varchar(1000)
set @s=’person A at 2006 345 15:24 See attachment from Ron Holden person B at 2006 361 16:20 Refer to AR 113831.’
select substring(@s,patindex(‘%[0-9]%’,@s),patindex(‘%:%’,@s)+2-patindex(‘%[0-9]%’,@s)+1)
Hi
First of all, thank you for taking time to write t-sql and publish it.
Second, I would like to point out that substring is 1 based instead of 0 based (in SQL 2000 Sp3 at least but should remain the same for other versions as well)
What does it mean? Little beside that 0 based loop one time more than it really need. (One loop is wasted)
The fix? Set @count = 1 for the starting condition instead of @count=0
Hi Pinal,
I always follow your statement which are really helpful in my work.
I am just wondering if you have function or modify the above function in reverse way. Meaning I would just like to extract the letters from Alphanumeric string.
Like I have a fields like.
AK48148
D144432
MT23432
DT55544
So I want a function that just extract the letters from it.
I would appreciate your time.
Thanks
Refer this and change [0-9] to [a-z]
@Syed,
I am sure there must be be another good way of doing the same thing…. I came up with this… This is HIGHLY performance killing query… since this is a while loop under while loop.
Its your choice if you want to use this…
This does exactly what you want.
Here you go…
— This is your main table
create table example123 ( names varchar(max))
go
— Inserting Sample Data
insert into example123 values ( ‘AK48148’)
insert into example123 values ( ‘D144432’)
insert into example123 values ( ‘MT23432’)
insert into example123 values ( ‘DT55544’)
insert into example123 values ( ‘12345’) — This will not be displayed in the output since it do no have any letter in it
— This is the output or result table
create table #results ( names varchar(100))
go
— This is our sample original table
create table #result ( id int identity, names varchar(100))
go
— We are copying your data into this sample original table
insert into #result select names from example123
go
— This is Actual Script
declare @var int
declare @var2 int
declare @car varchar(100)
declare @len int
declare @res varchar(100)
declare @result varchar(100)
declare @store varchar(100)
set @var = 1
while @var < = ( select count(*) from #result)
begin
select @car = names from #result where id = @var
set @result = ”
set @len = 1
while @len < = ( select len(@car) from #result where id = @var)
begin
select @store = substring ( @car, @len, len(@car)) from #result where id = @var
if ascii( @store) 64
begin
select @res = char(Ascii (@store)) from example123 where ascii( @store) 64
set @result = @res+@result
end
set @len= @len+1
end
if @result ”
begin
insert into #results select reverse(@result)
end
set @var= @var+1
end
select * from #results
drop table #results
drop table #result
drop table example123
— End of Script
I am not sure if there is any function already to do this, if you want to change the code, you are more than welcome, if something better can written for same, please post
Hope this helps,
Imran.
Heres an alternative method that seems to have better performance. The method described above took about 30 seconds over my test data whereas the method below takes about 11 seconds over the same data.
create function [dbo].[ExtractNumbers](@Numbers nvarchar(2000))
returns nvarchar(2000)
as
BEGIN
declare @NonNumericIndex int
set @NonNumericIndex = PATINDEX(‘%[^0-9]%’,@Numbers)
WHILE @NonNumericIndex > 0
begin
SET @Numbers = REPLACE(@Numbers,SUBSTRING(@Numbers,@NonNumericIndex,1),”)
set @NonNumericIndex = PATINDEX(‘%[^0-9]%’,@Numbers)
end
return @Numbers
END
@Syed,
Please replace the middle part of the query with this, I dont know how the query is changed, this is not the one I posted… I executed this query atleast 10 times befire posting…. There must be something wrong happened while posting my reply,
anyways I apologize for any inconvenience caused,
Please replace this query in the end of the code
– This is Actual Script
declare @var int
declare @var2 int
declare @car varchar(100)
declare @len int
declare @res varchar(100)
declare @result varchar(100)
declare @store varchar(100)
set @var = 1
while @var < = ( select count(*) from #result)
begin
select @car = names from #result where id = @var
set @result = ”
set @len = 1
while @len 64 and ascii(@store) 64 and ascii (@store) < 123 — This is the correct script
set @result = @res+@result
end
set @len= @len+1
end
if @result ”
begin
insert into #results select reverse(@result)
end
set @var= @var+1
end
select * from #results
drop table #results
drop table #result
drop table example123
– End of Script
Hope this helps.
Imran.
thank you for this article.
It was very helpful to me.
Moreno
SUBSTRING(@String,@Count,1) <= ‘9’
what is the purpose of 9 here?
@Reena,
Because the minimum digit is 0 and maximum digit is 9.
Regards,
IM.
how can i get only amount from this
50.00 on 04/06/2009
@Hema
select substring ( ‘50.00 on 04/06/2009’, 1, charindex (‘.’, ‘50.00 on 04/06/2009’)+2)
~IM
Other methods
declare @s varchar(100)
set @s='50.00 on 04/06/2009'
select substring ( @s, 1, charindex (' ', @s)-1)
select substring (@s , 1, patindex ('%[0] %', @s))
Also refer this post
I want to isolate alphanumeric values like
column a values 456abc123def
I want to sepateit
Where col like ‘%[0-9]%’ and col like ‘%[a-zA-Z]%’
plz help me i have a table which contained alpha numeric values like this 1235abcd7412 nofixed length and other row contained abcvd52a1a2
account
1235abcd7412
abcfd52a1a2
ghti5214ve3
asif214bv2
nofixed length account column plz show me easy query
thanks
asif
@asif
Please provide sample output.
Reply to Asif,
@Asif
CREATE FUNCTION [dbo].[UFN_Eliminate_Alphabet] (@string varchar(max))
RETURNS int
AS
BEGIN
DECLARE @result varchar(50)
DECLARE @position int
SET @position = 1
SET @result = ”
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT @result = @result + case when (ASCII(SUBSTRING(@string, @position, 1))) between 48 and 57 then SUBSTRING(@string, @position, 1)
else ''
end
SET @position = @position + 1
END
RETURN ( select convert ( int, @result))
END
GO
— Sample to Execute
select [dbo].[UFN_Eliminate_Alphabet]('abc64h5n5u')
GO
CREATE FUNCTION [dbo].[UFN_Eliminate_Numbers] (@string varchar(max))
RETURNS varchar (max)
AS
BEGIN
DECLARE @result varchar(50)
DECLARE @position int
SET @position = 1
Set @result = ''
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT @result = @result + case when ASCII(SUBSTRING(@string, @position, 1)) between 97 and 122 then SUBSTRING(@string, @position, 1)
when ASCII(SUBSTRING(@string, @position, 1)) between 65 and 90 then SUBSTRING(@string, @position, 1)
else ''
end
SET @position = @position + 1
END
RETURN ( select @result As ID )
END
GO
— Sample to Execute
select [dbo].[UFN_Eliminate_Numbers]('abc64h5n5u')
GO
Reply to Asif,
First function
CREATE FUNCTION [dbo].[UFN_Eliminate_Alphabet] (@string varchar(max))
RETURNS int
AS
BEGIN
DECLARE @result varchar(50)
DECLARE @position int
SET @position = 1
SET @result = ”
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT @result = @result + case when (ASCII(SUBSTRING(@string, @position, 1))) between 48 and 57 then SUBSTRING(@string, @position, 1)
else ''
end
SET @position = @position + 1
END
RETURN ( select convert ( int, @result))
END
GO
— Sample to Execute
select [dbo].[UFN_Eliminate_Alphabet]('abc64h5n5u')
~IM
how can i get all numeric values as a table ?
i mean, the result should be in a table like that..
Number
3
323
111
not scalar varchar value…
Thanks
You can use many methods
Refer this
Thanks a lot. Very helpful.
Hi all i need a small help,
I am having my string like this AP1BC_77.. I would like to display only 77 like that if we have any sort of string but i would like to display only the numeric appended to the end of the characters. Can any one help me please..
declare @s varchar(100)
set @s=’AP1BC_77′
select substring(@s,charindex(‘_’,@s)+1,len(@s))
I’m using your “integer extractor” function to output a temporary field which I’ll use to sort a list. only problem is, the output is varchar and therefore the numbers are sorting correctly. how can i convert the output to an INT datatype.
my query:
SELECT dbo.ExtractNumbers(pub) as newcol, title from test_table
order by newcol desc
disregard me first question, i figured it out:
SELECT cast(dbo.ExtractNumbers(pub)as int) as newcol, pub from test_table order by newcol desc