Extract only numbers from a string

Question

Extract only numbers from a string

LoosinMaMind

Hall of Fame

Points: 3366
More actions
June 30, 2014 at 8:16 am

#296117

Hi, I have no the following.
can someone help me tweak it so the i get both sets of numbers rather than just the first, these then ideally need to be displyed in seperate columns.
It may the case that there are more than two sets of numbers within the string but usually only two.
what i have at the moment.....
Declare @X varchar(100)
Select @X= 'Here is some text 15234 here is some more text 987654'
--
Select @X= SubString(@X,PATINDEX('%[0-9]%',@X),Len(@X))
Select @X= SubString(@X,0,PATINDEX('%[^0-9]%',@X))
--// show result
Select @X
This gives me 15234.
I would like to see 15234 in one column and 987654 in another (if more than one number in string then more columns would be required.
thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

What happens when you have more than 2 numbers?

You could use the Pattern Split explained on this article: http://www.sqlservercentral.com/articles/String+Manipulation/94365/

Declare @X varchar(100)

Select @X= 'Here is some text 15234 here is some more text 987654'

SELECT *

FROM dbo.PatternSplitCM( @X, '%[0-9]%')

WHERE Matched = 1

From here you just need to use cross tabs or pivot.

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

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 2

More than 2 numbers is where the problems lies. !!!

I would like to have them showing as separate fields but there should never be more than 5 sets of numbers in the original string.

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

LoosinMaMind (6/30/2014)
More than 2 numbers is where the problems lies. !!!
I would like to have them showing as separate fields but there should never be more than 5 sets of numbers in the original string.
Thanks

As Luis said, all you need from there is a cross tab. Take a look at the links in my signature for cross tabs.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

LoosinMaMind (6/30/2014)
Hi, I have no the following.
can someone help me tweak it so the i get both sets of numbers rather than just the first, these then ideally need to be displyed in seperate columns.
It may the case that there are more than two sets of numbers within the string but usually only two.
what i have at the moment.....
Declare @X varchar(100)
Select @X= 'Here is some text 15234 here is some more text 987654'
--
Select @X= SubString(@X,PATINDEX('%[0-9]%',@X),Len(@X))
Select @X= SubString(@X,0,PATINDEX('%[^0-9]%',@X))
--// show result
Select @X
This gives me 15234.
I would like to see 15234 in one column and 987654 in another (if more than one number in string then more columns would be required.
thanks in advance

Is the actual "some text" and "more text" the same on all the rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 5

In response to previous post....

unfortunately the numbers could be anywhere in the field and there could be up to a max of 5 numbers.

for example one field could be "abcd 12345 efghij 678910 klmnopqrst 111213"

another row may contain just "abcd 12345".

another row may contain just "ab 12 cdef 34567 ghi".

With this being a free text field within a database there is not set format to work with . All i know is that we need to extract only the numeric aspect of the field.

These could be extracted as a single field, perhaps comma separated or as separate fields themselves

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

LoosinMaMind (7/1/2014)
In response to previous post....
unfortunately the numbers could be anywhere in the field and there could be up to a max of 5 numbers.
for example one field could be "abcd 12345 efghij 678910 klmnopqrst 111213"
another row may contain just "abcd 12345".
another row may contain just "ab 12 cdef 34567 ghi".
With this being a free text field within a database there is not set format to work with . All i know is that we need to extract only the numeric aspect of the field.
These could be extracted as a single field, perhaps comma separated or as separate fields themselves
Thanks

So look at the article that Luis suggested. It will split out a string into any number of numeric values.

Given your example above...

create table #Something

(

SomeID int identity,

SomeValue varchar(2000)

)

insert #Something

select 'abcd 12345 efghij 678910 klmnopqrst 111213' union all

select 'abcd 12345' union all

select 'ab 12 cdef 34567 ghi'

SELECT *

from #Something s

cross apply dbo.PatternSplitCM(s.SomeValue, '%[0-9]%')

WHERE Matched = 1

drop table #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

Going further even if I don't like to give the complete solution. You could show some effort on trying the solutions and recommendations.

Using Sean's sample data:

SELECT SomeID,

SomeValue,

MAX( CASE WHEN ItemNumber IN(1,2) THEN Item END) Number1,

MAX( CASE WHEN ItemNumber IN(3,4) THEN Item END) Number2,

MAX( CASE WHEN ItemNumber IN(5,6) THEN Item END) Number3,

MAX( CASE WHEN ItemNumber IN(7,8) THEN Item END) Number4,

MAX( CASE WHEN ItemNumber IN(9,10) THEN Item END) Number5

from #Something s

cross apply dbo.PatternSplitCM(s.SomeValue, '%[0-9]%')

WHERE Matched = 1

GROUP BY SomeID, SomeValue

ORDER BY SomeID

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Jeff Moden (7/1/2014)
LoosinMaMind (6/30/2014)
Hi, I have no the following.
can someone help me tweak it so the i get both sets of numbers rather than just the first, these then ideally need to be displyed in seperate columns.
It may the case that there are more than two sets of numbers within the string but usually only two.
what i have at the moment.....
Declare @X varchar(100)
Select @X= 'Here is some text 15234 here is some more text 987654'
--
Select @X= SubString(@X,PATINDEX('%[0-9]%',@X),Len(@X))
Select @X= SubString(@X,0,PATINDEX('%[^0-9]%',@X))
--// show result
Select @X
This gives me 15234.
I would like to see 15234 in one column and 987654 in another (if more than one number in string then more columns would be required.
thanks in advance
Is the actual "some text" and "more text" the same on all the rows?

Heh... I know that you know what you're doing on this but I don't understand the value of doing this. How will you know what the numbers actually represent?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 9

Hi, once the number have been extracted from the free text field then they will be used against a reference table to give meaning.

I know that holding reference values in a free text field is not the correct way of doing this but this is something that is out of my control.

Thanks