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 ENDRETURN @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 (http://www.SQLAuthority.com)
pinaldave
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
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
@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.
@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