Extract Numeric From char 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 2000 Forums

SQL Server Development (2000)

Extract Numeric From char string

Author

Topic

henryparsons10
Starting Member

23 Posts

Posted - 2008-06-20 : 08:51:17

I have searched the forums and cannot find an answer, tho I am sure it is me that has missed it.

What I need to know is how to extract the numeric from a varchar string.

The fields contains data like so:

Job: 1 Stop:11:PodName: --> c fils

This shows that at stop number 11, c fils signed for the delivery. What I need is a way to pull out only the stop number (11) from this string. However, stops range from 1-20 so the actual length I need to extract varies from one character to two.

Help Please

singularity
Posting Yak Master

153 Posts

Posted - 2008-06-20 : 09:21:37

There's probably a more elegant way of doing this, but here's what I've been able to come up with:

SELECT
CASE
WHEN isnumeric(substring(yourfield, patindex('%Stop%', yourfield) + 5, patindex('%Stop%', yourfield) - 6)) = 1 THEN
substring(yourfield, patindex('%Stop%', yourfield) + 5, patindex('%Stop%', yourfield) - 6)
ELSE
substring(yourfield, patindex('%Stop%', yourfield) + 5, patindex('%Stop%', yourfield) - 7)
END AS Stop_Nbr
FROM yourtable

henryparsons10
Starting Member

23 Posts

Posted - 2008-06-20 : 09:38:20

I get

"invalid length parameter passed to the substring function"

Thx ahead of time for helping me here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 09:48:35

quote:
Originally posted by henryparsons10

I get

"invalid length parameter passed to the substring function"

Thx ahead of time for helping me here.

I guess this is because you've some string values without word 'stop' appearing in tour table. Are you sure you'll have 'stop' always?If not what should be value returned in those cases?

henryparsons10
Starting Member

23 Posts

Posted - 2008-06-20 : 09:50:48

I want to ignore fields that do not contain the word stop.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 10:10:50

quote:
Originally posted by henryparsons10

I want to ignore fields that do not contain the word stop.

then put a where to filter them out

WHERE patindex('%Stop%', yourfield)>0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-20 : 18:52:25

quote:
Originally posted by henryparsons10

I have searched the forums and cannot find an answer, tho I am sure it is me that has missed it.

What I need to know is how to extract the numeric from a varchar string.

The fields contains data like so:

Job: 1 Stop:11:PodName: --> c fils

This shows that at stop number 11, c fils signed for the delivery. What I need is a way to pull out only the stop number (11) from this string. However, stops range from 1-20 so the actual length I need to extract varies from one character to two.

Help Please

If you have data always in this format, you can try

select parsename(replace(replace(data,'job:',''),':','.'),3) from 
(
	select 'Job: 1 Stop:11:PodName: --> c fils' as data
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources