T-SQL : Find 8 number digit in string

Question

I am trying to get first 8 digit number in string. For example, need to get 49691234 in all below strings. Tried to use PatIndex & Substring functions but not getting result I desire. Pls advise

Example:

'hello world # 49691234 - black fox'
'black fox # 49691234'
'black fox 49691234 hello'

Welcome to Stack Overflow. Questions need a more specific example. Please review: https://stackoverflow.com/help/mcve — Twisty, Nov 28 '17 at 23:35
duplicate question: https://stackoverflow.com/questions/25893714/find-if-there-is-a-6-digit-number-within-a-string, just use 8 instead of 6. read all answers. — Gholamali Irani, Nov 28 '17 at 23:54
Possible duplicate of [find if there is a 6 digit number within a string](https://stackoverflow.com/questions/25893714/find-if-there-is-a-6-digit-number-within-a-string) — HABO, Nov 29 '17 at 03:38
Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Nov 29 '17 at 03:39
And what happens when your number is not separated from other characters by a space? E.g., when you have "black fox #12345678"? If that number is important, then you should fix your schema and isolate it when the row is inserted. — SMor, Nov 29 '17 at 15:38

score 1 · Answer 1 · answered Nov 28 '17 at 23:50

1

You can use patindex():

select substring(col, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', col), 8),
from t;

answered Nov 28 '17 at 23:50

Gordon Linoff

1,242,037
58
646
786

Pure simple SQL, I love it! – Mark Kram Nov 29 '17 at 20:02

score 0 · Answer 2 · answered Nov 29 '17 at 19:57

You can also use ngrams8k.

-- sample data
declare @yourtable table(txt varchar(40));
insert @yourtable values
('hello world # 49691234 - black fox'),
('black fox # 49691234'),
('black fox 49691234 hello');

-- solution
select * 
from @yourtable t
cross apply dbo.NGrams8k(t.txt,8)
where token not like '%[^0-9]%';

Results

txt                                      position             token
---------------------------------------- -------------------- ----------
hello world # 49691234 - black fox       15                   49691234
black fox # 49691234                     13                   49691234
black fox 49691234 hello                 11                   49691234

T-SQL : Find 8 number digit in string

2 Answers2