Extract Numbers from a String using Sql Server 2005

I have seen a common requirement where we have numbers and alphabets mixed up in a column (house no in the address field) and the number has to be extracted out of the string.

Here's a query to do so:

-- This query is a Modification of the Original query by 'usenetjb'

DECLARE @NumStr varchar(1000)
SET @NumStr = 'This string contains 5 words and now 9 words';

BEGIN
WHILE PATINDEX('%[^0-9]%',@NumStr)> 0
SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')
END

PRINT @NumStr
at

9 comments:

  1. AnonymousApril 22, 2008 at 5:35 AM

    i did not understand a damn thing what r u talk'n about

    ReplyDelete
  2. Suprotim AgarwalApril 22, 2008 at 7:30 PM

    Hi, Did you try that query?

    If you have a string mixed with alphabets and numbers, somethng like this "'This string contains 5 words and now 9 words'" and now you want to extract only the numbers 5 and 9, use the query shown above.

    ReplyDelete
  3. AnonymousApril 23, 2008 at 5:25 AM

    How can I use this to check if the mixed string does indeed have numbers in it?

    ReplyDelete
  4. Suprotim AgarwalApril 23, 2008 at 7:53 PM

    Hi, Can you give an example of how do you percieve a mixed string to be. The solution does contain a string that has numbers and characters. I would need you to explain me your requirement with some details.

    ReplyDelete
  5. AnonymousApril 24, 2008 at 12:52 PM

    For example if i have the following string:

    'abcde99jtk'

    I need to check if the string above has a number. If so, it returns TRUE. If not, it returns FALSE.

    ReplyDelete
  6. Suprotim AgarwalApril 24, 2008 at 10:41 PM

    Thanks for your comment. Check the answer over here:

    http://www.sqlservercurry.com/2008/04/how-to-check-if-string-contains-numbers.html

    ReplyDelete
  7. AnonymousMay 14, 2008 at 2:51 AM

    I need to select only rows where a character field contains exactly 4 numbers, blanks allowed but replaced and no alphabetic characters allowed. There are thousands of rows and I need to do this in one select. Can it be done without creating a user function or stored procedure?

    ReplyDelete
  8. SmittyDecember 23, 2009 at 11:56 AM

    FYI... the method described here seems to work faster than the one described here - http://blog.sqlauthority.com/2007/04/11/sql-server-udf-user-defined-function-to-extract-only-numbers-from-string/

    Just a heads up for anyone looking for performance.

    ReplyDelete
  9. BladeDecember 27, 2009 at 10:12 PM

    Thanks Smitty. I checked the execution plan and it is faster.

    ReplyDelete