T-SQL Extract Numbers from a string and everything in between

Question

I'm using SQL Server 2014.

I understand how to extract numbers from a string using PATINDEX (Query to get only numbers from a string), however, how would I also include if they was actually a string value between the numbers that I also need to extract. e.g.;

This is my string:

15-19 Smith Street
1-3 Smith Street

And I need:

15-19
1-3

Also, if there is a suffix I need to extract that as a separate field:

147a York Road

I would need 147 as 1 field and the letter 'a' as another

How do I achive this in T-SQL?

We'd love to see you attempt in order to assist you with this task. — Dale K, May 15 '20 at 10:17
How well structured are your address fields? Could you use the initial space for the first part and then strict alphabetic for the second part. — Peter Smith, May 15 '20 at 10:36
What happens if you have `'Flat 1, 7-12 North Street'`? How about `'101-107 21st Street'`? Honestly, using T-SQL for this seems like a bad idea, string manipulation is far from it's forté; especially without native REGEX support. — Thom A, May 15 '20 at 10:41

Yogesh Sharma · Accepted Answer · 2020-05-15T10:15:45.123

2

You can use charindex() with patindex():

select substring(col, patindex('%[0-9]%', col), 
                      len(col) - charindex(' ', col, patindex('%[0-9]%', col)) - 1
                ) as col

edited May 15 '20 at 10:15

answered May 15 '20 at 10:08

Yogesh Sharma

49,870
5
26
52

score 1 · Answer 2 · answered May 15 '20 at 10:50

1

You can use the below Coding into an USER-DEFINED FUNCTION.

It can satisfy all your 3 cases[15-19 Smith Street,1-3 Smith Street,147a York Road]

DECLARE @strAlphaNumeric VARCHAR(MAX)='15-19 Smith Street'

DECLARE @intAlpha INT  
SET @intAlpha = PATINDEX('%[a-z]%', @strAlphaNumeric)
BEGIN  
WHILE @intAlpha >0
BEGIN  
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )  
SET @intAlpha = PATINDEX('%[a-z]%', @strAlphaNumeric ) 
END  
END  

select  ISNULL(@strAlphaNumeric,0)

answered May 15 '20 at 10:50

Thiyagu

1,260
1
5
14

1

Its great you're so active on SO, but please avoid the use of images. We are always asking people to remove images and use formatted text in their questions. So best to lead by example. And if you really have to use images, just show the data, not the query. Much easier to read that way. – Dale K May 15 '20 at 10:54
1

I must admit, I'm really not a fan of using a `WHILE` here. SQL is a set based langauge, so performs poorly at iterative processes like this. Trying to implement this on a (large) dataset will have some terrible performance. – Thom A May 15 '20 at 11:16
For this case, i am sure that the while does not performs so poorly. Because these String are not too long.The Loop executes almost 3 to 5 times of a string. Even though we use this coding inside a UDF function. So i think this cannot perform Poor. --@Larnu – Thiyagu May 15 '20 at 11:22

score 0 · Answer 3 · answered May 15 '20 at 10:41

It might seem a bit convoluted, but you could use Common Table Expressions (CTE) and recursion to do it. The CTE loop over the characters in the string and keep adding them to the output columns in either column depending on what they are.

declare @a table  (v varchar(20))

insert into @a
values
('1-15 Something'), ('147a Something else'),
('15 dkdkjg'), ('154'),
('15-3'), ('16-9 Some lane')

update @a set v = trim(v);

with
cte as 
(select v, max(len(v)) over () maxLen, 1 curr, 0 final, 
        cast('' as varchar(100)) st, cast('' as varchar(100)) st2
   from @a
union all
 select v, maxLen, curr+1 curr, 
        iif(len(v) = curr or substring(v, curr+1, 1) = ' ', 1, 0) final,

        cast(iif(substring(v, curr, 1) like '[0-9]'
           or substring(v, curr+1, 1) like '[0-9]', 
           concat(st, substring(v, curr, 1)), st) as varchar(100)) st, 

        cast(iif(substring(v, curr, 1) not like '[0-9]' 
           and substring(v, curr+1, 1) not like '[0-9]', 
           concat(st2, substring(v, curr, 1)), st2) as varchar(100)) st2

  from cte
 where final = 0) 

select v, st, st2
  from cte
 where final = 1

T-SQL Extract Numbers from a string and everything in between

3 Answers3