By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

Extract Numeric Data from text field

code green
Expert 100+
P: 1,726
I have a text field called notes in which users have typed almost anything they like.
I need to find numeric data randomly inserted in there.
So for example there may be a telephone number and an account number
in amongst varied text.

How do I extract this data in a SELECT query in the format
Expand|Select|Wrap|Line Numbers
  1. id       number
  2. xx      0107364561  2124
OR
Expand|Select|Wrap|Line Numbers
  1. id       number
  2. xx      0107364561
  3. xx      2124
I have found a function called ExtractInteger()
which returns the numeric data in the format
Expand|Select|Wrap|Line Numbers
  1. 01073645612124.
Nearly there but not quite.
I suppose in the meantime I will try to improve on this function
unless someone has a solution
May 13 '09 #1
Share this Question
Share on Google+
4 Replies


Uncle Dickie
P: 67
Would you be able to use a function like this to get you what you need?

Expand|Select|Wrap|Line Numbers
  1. DECLARE @Output nvarchar(50)
  2. SET @Output = ''
  3.  
  4. WHILE patindex('%[0123456789]%', @sub) <> 0
  5.   BEGIN
  6.     SET @Output = @Output + substring(substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)),1,patindex('%[^0123456789]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)))-1) + ','
  7.     SET @sub = substring(substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)),patindex('%[^0123456789]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub))),len(@sub))
  8.   END
  9. RETURN @Output
  10.  
I have used a comma to delimit the numeric parts of the @sub passed to the function
May 14 '09 #2

Uncle Dickie
P: 67
Just thought I would comment on the two lines in the WHILE statement while I remember what I did:
  1. Find the position of the 1st numeric character (using patindex)
  2. Create a substring (a1) starting from this point
  3. Search a1 for first non-numeric character (again using patindex)
  4. Create substring (a2) starting from 1st character of a1, ending 1 character before the first non-numeric

The above 4 points are all incorporated in the first line where @Output is set

The second line basically does the same thing but instead of creating substring a2, it creates a substring from the first non-numeric character in a1.

This is then plugged back into the WHILE loop
May 14 '09 #3

code green
Expert 100+
P: 1,726
Thanks for the help.
I need to take this project a step further now so I will post a similar question
May 15 '09 #4

ck9663
Expert 2.5K+
P: 2,878
String parsing :) Either way, I think it'll have the same speed.

Happy Coding!


--- CK
May 15 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.