By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (record_id int, txt varchar(100))
insert into t values
(1, 'Sanchez, Raul - POS - OS - 489-849-7894 - AB - Conf')
,(2, 'Smith, Nancy RL 1458968-rl')
,(3, 'Sudhu, Mandeep intake # 78596/rl')
,(4, '1887569 Smith, Jonathan-ESK')
,(5, 'Montana, Joe/1451252rm')
,(6, 'Johnson, Fredy/1784569/78563/RG')
,(7, 'here 336464564 $#$# is 77667 something a little bit more 45676876876 challenging')
,(8, '12345')
,(9, '1111 22222 33333')
,(10, 'fdgfdgdgddf')
10 rows affected
with
t1
as
(
select record_id
,txt
,trim(translate(txt, '1234567890', space(10))) as non_digits
from t
),
t2
as
(
select record_id
,trim(translate(txt, non_digits, space(len(non_digits)))) as spaced_numberes
from t1
),
t3
as
(
select record_id
,replace(replace(replace(spaced_numberes, ' ', ' *'), '* ', ''), '*', '') as space_delimited_numbers
from t2
)
select t3.record_id
,ss.value
from t3
cross apply string_split(space_delimited_numbers, ' ') as ss
| record_id | value |
|---|---|
| 1 | 489 |
| 1 | 849 |
| 1 | 7894 |
| 2 | 1458968 |
| 3 | 78596 |
| 4 | 1887569 |
| 5 | 1451252 |
| 6 | 1784569 |
| 6 | 78563 |
| 7 | 336464564 |
| 7 | 77667 |
| 7 | 45676876876 |
| 8 | 12345 |
| 9 | 1111 |
| 9 | 22222 |
| 9 | 33333 |
| 10 |