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';

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


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook


Anonymous said...

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

Suprotim Agarwal said...

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.

Anonymous said...

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

Suprotim Agarwal said...

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.

Anonymous said...

For example if i have the following string:


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

Suprotim Agarwal said...

Thanks for your comment. Check the answer over here:


Anonymous said...

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?

Smitty said...

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.

Blade said...

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