CODEWITHSUNDEEP
sql-servert-sqlsubstring
piyush
piyush

Reputation: 1

T-SQL : Find 8 number digit in string

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'

Upvotes: 0

Views: 2959

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 1

Related Questions