SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Extracting number from a text string		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tom85
Starting Member

13 Posts
Posted - 11/13/2012 :  07:34:32  Show Profile  Reply with Quote
Hello,

I am trying to extract the number from a text string that is sent to us by the client. Unfortunately it is part of the note that is typed in by the users and follows no particular pattern.

for example: 14 box 003541903 2whatsup 588
(003541903 being the number I am after)

This number does not always have the same number of digits and the leading zeros can be trimmed. The most reliable way of doing this would be probably to extract numbers minimum of 5 digits long (please note that position of the number within the text string differs too)

Is this even doable?

Any help would be much appreciated,

Thank you,
Kind Regards,
Tom

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/13/2012 :  07:51:30  Show Profile  Reply with Quote
It is somewhat messy, but you can do it something like shown below
DECLARE @x VARCHAR(32) = '14 box 003541903 2whatsup 588';
SELECT SUBSTRING(STUFF(@x,1,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%',@x),''),
	1,PATINDEX('%[^0-9]%',STUFF(@x,1,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%',@x),'')))
Go to Top of Page

Tom85
Starting Member

13 Posts
Posted - 11/13/2012 :  09:05:09  Show Profile  Reply with Quote
This worked,
Thank you very much!!!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/13/2012 :  10:05:18  Show Profile  Reply with Quote
You are quite welcome :)

You may want to test boundary cases such as when there are no characters following the number sequence etc. and make appropriate changes to account for such cases.
Go to Top of Page

Tom85
Starting Member

13 Posts
Posted - 11/13/2012 :  11:03:55  Show Profile  Reply with Quote
Yeah, It does need some tweaking, I'm just trying to wrap my head around it now (My OCD kicked in, I'm gonna waste another day staring at it, I know it!).
The important thing is that you gave me the code I can work with, I spent good few hours building that frustration - I was about to loose it.

Thanks again, you're a star!

Kind Regards,
Tom


Regards,
Tom
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 11/17/2012 :  07:25:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
There are three posts and choose whichever applicable to you
http://beyondrelational.com/modules/2/blogs/70/posts/10946/extract-only-numbers-from-a-string-part-3.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000