Extracting number from a text string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Extracting number from a text string

Author

Topic

Tom85
Starting Member

13 Posts

Posted - 2012-11-13 : 07:34:32

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 07:51:30

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),'')))

Tom85
Starting Member

13 Posts

Posted - 2012-11-13 : 09:05:09

This worked,
Thank you very much!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 10:05:18

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.

Tom85
Starting Member

13 Posts

Posted - 2012-11-13 : 11:03:55

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-17 : 07:25:05

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources