Posted by KenJ on 01/12/06 05:45

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 ***

• Next in forum: Re: Convert Text to Int
• Prev in forum: mesage in Sql Server 2000 log
• Thread view: Re: selecting only numeric data from a string

[Reply to this message]