selecting only numeric data from a string

[Tim Groulx]

Hello,

I need to be able to select only the numeric data from a string that is
in the form of iFuturePriceID=N'4194582'

I have the following code working to remove all the non-numeric text
from before the numbers, but it is still leaving the single quote after
the numbers, i.e. 4194582'

Any ideas or suggestions how to accomplish that? Thanks in advance.

Declare @TestData varchar(29)
Set @TestData = "iFuturePriceID=N'4194582'"
Select Substring(@TestData, patindex('%[0-9]%', @TestData),
Len(@TestData))

TGru

*** Sent via Developersdex http://www.developersdex.com ***

[KenJ]

len(@TestData) causes it to take the entire string after the first
number. you need to trim this down by the length of your number...

SELECT substring(@TestData, patindex('%[0-9]%', @TestData),
len(@TestData) - patindex('%[0-9]%', @TestData) - patindex('%[0-9]%',
reverse(@TestData)) + 2)

Tim Groulx wrote:

Hello,

I need to be able to select only the numeric data from a string that is
in the form of iFuturePriceID=N'4194582'

I have the following code working to remove all the non-numeric text
from before the numbers, but it is still leaving the single quote after
the numbers, i.e. 4194582'

Any ideas or suggestions how to accomplish that? Thanks in advance.

Declare @TestData varchar(29)
Set @TestData = "iFuturePriceID=N'4194582'"
Select Substring(@TestData, patindex('%[0-9]%', @TestData),
Len(@TestData))

TGru

*** Sent via Developersdex http://www.developersdex.com ***

[tgru]

The issue there is that the numbers are of varying lengths, and it might
not always just be a single quote at the end. So, I was hoping to be
able to just extract the numerics without having to deal with positions
and length of the numeric portion. I.e. why can't I just grab alpha or
numeric or non alpha numeric from the whole string??
:-) Seems like that should be easier to do than it is...

TGru

*** Sent via Developersdex http://www.developersdex.com ***

[Robert Klemme]

tgru wrote:

The issue there is that the numbers are of varying lengths, and it
might not always just be a single quote at the end. So, I was hoping
to be able to just extract the numerics without having to deal with
positions and length of the numeric portion. I.e. why can't I just
grab alpha or numeric or non alpha numeric from the whole string??
:-) Seems like that should be easier to do than it is...

You can for example use charindex to find the terminating single quote.

You cal also install Oracle 10 which supports regular expressions.
Honestly, is this stored in the DB? If so, I'd try to make it two columns
if possible.

HTH

robert