Returning a Number from a string with a predefined length in T-SQL

Question

I have an address field like

'12345 dummycity 14'
'NL - 54321 City'
'12 City4  32154'

I want to extract only the substring where there are 5 numeric characters in a row.

Anybody has a clue how to extract this in T-SQL?

score 1 · Accepted Answer · answered Apr 24 '19 at 14:45

1

Such string processing is a little tricky in SQL Server, but patindex() does much of the work for you:

select left(stuff(str, 1, patindex('%[0-9][0-9][0-9][0-9][0-9]%', str) - 1, ''), 5)
from (values ('NL - 54321 City')) v(str)

answered Apr 24 '19 at 14:45

Gordon Linoff

that was fast, thank u very much! I was on the good way but didn't come up to define my pattern as [0-9] 5 times! – Frederik De Clercq Apr 24 '19 at 14:49

1 Answers1