Urgent! - How to extract number from a string

  • Hi,

    You can try the following query :

    select case isnumeric(YourFieldName) when 1 then YourFieldName else 0 end.

    Hope that Helps 🙂

  • Hi,

    You can try the following query :

    select case isnumeric(YourFieldName) when 1 then YourFieldName else 0 end.

    Avaneesh,

    Try this and see why you shouldn't use ISNUMERIC as an "IsAllDigits" function...

    SELECT ISNUMERIC('1e3')

    SELECT ISNUMERIC('1d3')

    SELECT ISNUMERIC('$1,000.00')

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • There is a problem with using ISNumeric to decipher a string. Certain character groups return TRUE such as 10E. So if in this case someone lives at apartment 10E you would get a positive result for 10E when only 10 waas wanted or expected.

    I think the solutions proposed by Matt & Jeff are as close as you can get with this type of mess. Software can only do so much to clean up unknown data, and no matter how you decide to squeeze the results you'll always leave something out.

  • How about this. Might be a bit long winded though. This will pull out 742.0 of the following strings

    'On 742.0 Evergreen Terrace'

    'The number is at the end 742.0'

    ' 742.0 Evergreen Terrace 1234'

    It only picks up the first full number it comes across and then exits the loops. Though if your using null values you may have to play with it.

    declare @STR varchar(100)

    declare @Tmp varchar(1)

    declare @numstr varchar(100)

    declare @count int

    declare @fndnum binary

    select @STR=' 742.0 Evergreen Terrace'

    select @count=1,@fndnum=0,@numstr=''

    while @fndnum=0

    begin

    select @Tmp=right(left(@str,@count),1)

    print @Tmp

    select @count=@count+1

    if isnumeric(@tmp)=1

    while isnumeric(@tmp)=1

    begin

    select @numstr=@numstr+@tmp

    select @Tmp=right(left(@str,@count),1)

    print @numstr

    select @count=@count+1

    select @fndnum=1

    end

    if @count>len(@str) set @fndnum=1

    end

    select 'I have found the number of ' + @numstr



    Nuke the site from orbit, its the only way to be sure... :w00t:

  • thank you very much..

  • I came across the same situation and I found the working solution for it.

    Here is the link:

    http://www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx

  • 🙂 God bless you!

  • KMM-489657 (11/15/2007)


    I was just reading thru this thread and I have a similar issue. I'm trying to extract the number string from a StarRating of Hotels column. For example the column currently has

    StarRating:

    0

    1

    1.5

    NA

    2

    2 STAR

    3.00

    4-STAR

    4

    NULL

    What I would like to see as the results in this column is:

    0

    1

    1.5

    0

    2

    2

    0

    3.00

    4

    4

    0

    And if possible replace the blank,NA and NULL with 0.

    Could someone please assist.

    ISNULL(NULLIF(LEFT(StarRating,PATINDEX('%[^0-9.]%',StarRating+' ')-1),''),'0')

    Far away is close at hand in the images of elsewhere.
    Anon.

  • initials_mi (9/18/2014)


    I came across the same situation and I found the working solution for it.

    Here is the link:

    http://www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx

    A set-based solution that does not use a loop is the faster way to go. In addition to what Jeff posted you could use the function discussed here: http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx

    or PatExclude8K (referenced in my signature)

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • This may be useful.

    DECLARE @strNumbers VARCHAR(100) = 'Final Figure 2008 and Qtr 2'

    DECLARE @Pos SMALLINT = 0

    SET @Pos = PATINDEX('%[^0-9]%', @strNumbers) --Find first character

    WHILE (@Pos > 0)

    BEGIN

    -- Replace alphabet with empty string.

    SET @strNumbers = STUFF(@strNumbers, @Pos, 1, '')

    -- Find next alphabet

    SET @Pos = PATINDEX('%[^0-9]%', @strNumbers)

    END

    SELECT @strNumbers [Output]

  • dva2007 - Tuesday, July 10, 2018 5:03 AM

    This may be useful.

    DECLARE @strNumbers VARCHAR(100) = 'Final Figure 2008 and Qtr 2'

    DECLARE @Pos SMALLINT = 0

    SET @Pos = PATINDEX('%[^0-9]%', @strNumbers) --Find first character

    WHILE (@Pos > 0)

    BEGIN

    -- Replace alphabet with empty string.

    SET @strNumbers = STUFF(@strNumbers, @Pos, 1, '')

    -- Find next alphabet

    SET @Pos = PATINDEX('%[^0-9]%', @strNumbers)

    END

    SELECT @strNumbers [Output]

    A WHILE loop is a terrible way to do this, even for a problem that is 11 years old. 🙂

    There was at least 1 set based solution that have been posted which would be far better.

    Thom~

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

  • I already used the function given by Jeff dbo.fnLeaveOnly. Both of these gives me expected result however I found the solution I posted is quicker. We already had this posted function in data warehouse.

    I suppose it depends on what kind of dataset we are using. The dataset I am using has only 200 rows and function fnLeaveOnly took 3 seconds to return data and the one I posted took less than a second.

  • dva2007 - Tuesday, July 10, 2018 5:24 AM

    I already used the function given by Jeff dbo.fnLeaveOnly. Both of these gives me expected result however I found the solution I posted is quicker. We already had this posted function in data warehouse.

    I suppose it depends on what kind of dataset we are using. The dataset I am using has only 200 rows and function fnLeaveOnly took 3 seconds to return data and the one I posted took less than a second.

    You should have a look at these functions
    😎

  • dva2007 - Tuesday, July 10, 2018 5:24 AM

    I already used the function given by Jeff dbo.fnLeaveOnly. Both of these gives me expected result however I found the solution I posted is quicker. We already had this posted function in data warehouse.

    I suppose it depends on what kind of dataset we are using. The dataset I am using has only 200 rows and function fnLeaveOnly took 3 seconds to return data and the one I posted took less than a second.

    Please post the function that you settled on.  Thanks.

    And, agreed on the dbo.fnLeaveOnly... it's old stuff (scalar instead of iTVF) that I've replaced many years ago.
    .

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • After reading the article above I used dbo.DigitsOnlyEE which is very quick.

Viewing 15 posts - 16 through 30 (of 33 total)

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