Extract number from string

Transact-SQL
1

hello,
I have a table that includes a culumn whith string
I want to extract the number from the string
The numbers are not in fixed places.
below is tne table sample

name
jon 0294
tran jon 0294
transfer from jon 0294
0294 jon

below is tne table script

create table b2 (name varchar(255))

insert into b2 select 'jon 0294'
insert into b2 select 'tran jon 0294'
insert into b2 select 'transfer from jon 0294'
insert into b2 select '0294 jon'

below is the expected rusults

| name | number |
| jon 0294 | 0294 |
| tran jon 0294 | 0294 |
| transfer from jon 0294 | 0294 |
| 0294 jon | 0294 |

2

Can you try this

SELECT name as [name],SUBSTRING(name, PATINDEX('%[0-9]%', name), PATINDEX('%[0-9][^0-9]%', name + 't') - PATINDEX('%[0-9]%',
name) + 1) AS [Number]
FROM b2

3

Thanks for your response.
also,
Does the expression below represent the last numeric character in the string?
I would appreciate it if you could explain the logic of the expression

PATINDEX('%[0-9][^0-9]%', name + 't')

4

Not the last numeric character but continuous series of numbers.
If the requirement where your string would be something like this 1231erder2534, it will only pull the first continuous numeric characters 1231 and not 2534.

5

hi

hope this helps

select 
     name,value 
from 
   b2 cross apply    string_split(name,' ') 
where 
  value NOT LIKE '%[a-zA-Z]%'

image

2 Likes
6

can you please explain why there is "+t" in the function?

7

Hi

Hope this helps

Another Way of doing this ( TRANSLATE Function )

create data script

drop table b2
create table b2 (name varchar(255))

insert into b2 select 'jon 0294'
insert into b2 select 'tran jon 0294'
insert into b2 select 'transfer from jon 0294'
insert into b2 select '0294 jon'

SELECT 
    name 
	, replace(translate(name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ ','                           '),' ','') 
FROM 
  b2

image