need script to pull out all numeric numbers more that 6 digits long within a string

Question

need script to pull out all numeric numbers more that 6 digits long within a string

Snargables

SSCrazy Eights

Points: 8855
More actions
July 13, 2017 at 9:38 am

#338856

IF Object_id('tempdb..#temp') IS NOT NULL

DROP TABLE #temp
select 1 id, 'hello 123 fff 1234567 and today;""o999999999 tester 44444444444444 done' strlist

into #temp

union all

select 1 id, 'hello 654 olk k uu 7654321 and today 222222222 tester 222222222444 done'

union all

select 2 id, 'syat 123 ff tyui( 1234567 and today 999999999 tester 44444444444444 done' strlist

union all

select 2 id, '&**OOOOO=+ + + // ==?7654321// and today [][]!!222222222\\\tester{}))222222222444 done'

--results should look like the below

--1, 1234567

--1, 999999999

--1, 44444444444444

--1, 7654321

--1, 222222222

--1, 44444444444444

--2, 1234567

--2, 999999999

--2, 44444444444444

--2, 7654321

--2, 222222222

--2, 222222222444

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

Something readable:
IF Object_id('tempdb..#temp') IS NOT NULL DROP TABLE #temp select 1 id, 'hello 123 fff 1234567 and today;""o999999999 tester 44444444444444 done' strlist into #temp union all select 1 id, 'hello 654 olk k uu 7654321 and today 222222222 tester 222222222444 done' union all select 2 id, 'syat 123 ff tyui( 1234567 and today 999999999 tester 44444444444444 done' strlist union all select 2 id, '&**OOOOO=+ + + // ==?7654321// and today [][]!!222222222\\\tester{}))222222222444 done' --results should look like the below --1, 1234567 --1, 999999999 --1, 44444444444444 --1, 7654321 --1, 222222222 --1, 44444444444444 --2, 1234567 --2, 999999999 --2, 44444444444444 --2, 7654321 --2, 222222222 --2, 222222222444

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

This is easy using the Pattern Splitter by Chris Morris that can be found on the following article: Splitting Strings Based on Patterns - SQLServerCentral

Here's the code that you can use once you've got the function.
SELECT id, Item FROM #temp CROSS APPLY dbo.PatternSplitCM(strlist,'%[0-9]%') s WHERE LEN(s.Item ) > 6 AND s.Matched = 1;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Snargables SSCrazy Eights Points: 8855 More actions · Answer 3

thank you. That reference worked perfectly for my needs