sql server (alternate):
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)
[quoted text, click to view] 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 ***
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
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
[quoted text, click to view] 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
