Urgent! - How to extract number from a string

  • Avaneesh -388582

    SSChasing Mays

    Points: 657

    Hi,

    You can try the following query :

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

    Hope that Helps 🙂

  • Jeff Moden

    SSC Guru

    Points: 995150

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Robert O'Byrne

    SSCommitted

    Points: 1917

    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.

  • EvilPostIT

    SSCrazy

    Points: 2578

    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:

  • phaniraj1987

    SSC Journeyman

    Points: 86

    thank you very much..

  • initials_mi

    SSC Rookie

    Points: 25

    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

  • onwukanjown

    Grasshopper

    Points: 23

    🙂 God bless you!

  • David Burrows

    SSC Guru

    Points: 64592

    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.

  • Alan Burstein

    SSC Guru

    Points: 61067

    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

  • dva2007

    SSCertifiable

    Points: 7703

    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]

  • Thom A

    SSC Guru

    Points: 98460

    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.

  • dva2007

    SSCertifiable

    Points: 7703

    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.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182369

    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
    😎

  • Jeff Moden

    SSC Guru

    Points: 995150

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • dva2007

    SSCertifiable

    Points: 7703

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

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

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