Hi All,
I have a table in which has a Notes field.
Each of these notes field has a phone number - eg. "Please provide
number 1234 to user XYZ."
I need to extract this number from each of the Notes field.

Can anybody tell me how to extract a number from a string?...

TIA!!!

Hello, snigs

If you want to extract the first number from a string and the number
does not contain any punctuation in it, you can try something like
this:

SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable

If you want to extract all the numbers from a string, including any
punctuation found inside the number, you can try something like this:

SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable

Razvan

PS. With this occasion, I would like to submit my two entries for the
"most unreadable query of the month" contest

On 6 Dec 2005 12:07:18 -0800, Razvan Socol wrote:

Hi Razvann,

Month, year, century - you win them all! ;-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)