470,874 Members | 1,831 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,874 developers. It's quick & easy.

Parse a numeric string from a field

Hello All,

I'm trying to parse for a numeric string from a column in a table. What
I'm looking for is a numeric string of a fixed length of 8.
The column is a comments field and can contain the numeric string in
any position
Here's an example of the values in the column
1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-98
2) wed.kx10/26 Netrez 95860536
Now I need to parse through these lines and return only the 8 digit
numbers in it
The result set should be
27068935
95860536

This is what I've done so far
Declare @tmp table
(
Comments_Txt varchar(255)
)

Insert into @tmp

select Comments_Txt from Reservation

select * FROM @tmp where Comments_Txt
like ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')

But it returns the entire comments field in the result set. What I need

is a way to return just those 8 digits.

Any Ideas??
Thanks in advance!!!

Jul 23 '05 #1
2 2337
You could use the following:

select substring(comments_txt,
patindex('%[0-9][0-9][0-9][0-9][0-9][0***9]%', comments_txt), 8)
from @tmp where Comments_Txt
like ('%[0-9][0-9][0-9][0-9][0-9][0***9]%')

--
David Rowland
dbmonitor.tripod.com

Jul 23 '05 #2
Thank You very much !!! That was just what I needed.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Jon Davis | last post: by
5 posts views Thread by Navid Azimi | last post: by
29 posts views Thread by gs | last post: by
7 posts views Thread by john.ford | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.