Numeric Extraction - SQL Server Forums

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

Transact-SQL (2000)

Numeric Extraction

Author

Topic

parand
Starting Member

8 Posts

Posted - 2008-07-07 : 06:00:12

Is there any function in SQL2000 which return only the numeric part of a alphanumeric field.

For example I would like to be able to convert the following:

60ml
10
4 x 125g
500ml
1 x 30ml
185ml
28
500ml

to:

60
10
4
500
1
185
28
500

Your help is deeply appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-07 : 06:16:16

[code]-- Prepare sample data
declare @t table
(
a varchar(40)
)

insert @t
select '60ml' union all
select '10' union all
select '4 x 125g' union all
select '500ml' union all
select '1 x 30ml' union all
select '185ml' union all
select '28' union all
select 'NoNumber' union all
select '500ml'

-- Final Query
select substring(a, 1, case when NonNumIndex = 0 then datalength(a) else NonNumIndex-1 end)
from
(
select a, patindex('%[a-z]%', a) as NonNumIndex from @t
) t
where NonNumIndex > 1 or NonNumIndex = 0[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

parand
Starting Member

8 Posts

Posted - 2008-07-07 : 06:48:11

Thanks very much.
I was hoping to find out if there is any string extraction function which would return the numbers before the first non-numeric character!
There might not be such a function!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources