Extract numerics - 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 2005 Forums

Transact-SQL (2005)

Extract numerics

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-04 : 02:24:58

Hi Guys,

I need to extract the first part of field which contains numerics
i.e.
CHQ: 10 780213
************** 1 53
AMEX T.C.STORE 9
AMEX T.C.STORE 10 33
375 17
PERSON
CARD 30 PAID

From the above examples I would need to extract:
10
1
9
10
375
PERSON
30

Firstly I have found a function which allows me to only get the integers (but does not keep the above formatting i.e. removes blanks)

http://blog.sqlauthority.com/2007/04/11/sql-server-udf-user-defined-function-to-extract-only-numbers-from-string/

Can anybody help with my above requirement please?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 04:06:04

select data, substring(numbers,1,patindex('%[ ]%',numbers+' ')-1) as new_data from
(
select data,substring(data,patindex('%[0-9]%',data),len(data)) as numbers from
(
select 'CHQ: 10 780213' as data union all
select '************** 1 53' union all
select 'AMEX T.C.STORE 9' union all
select 'AMEX T.C.STORE 10 33' union all
select '375 17' union all
select 'PERSON' union all
select 'CARD 30 PAID'
) as t
) as t

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-04 : 05:51:57

'PERSO'?

select data, substring(numbers,1,patindex('%[ ]%',numbers+' ')-1) as new_data from
(
select data,substring(data,patindex('%[0-9]%',data),len(data)+1) as numbers from
(
select 'CHQ: 10 780213' as data union all
select '************** 1 53' union all
select 'AMEX T.C.STORE 9' union all
select 'AMEX T.C.STORE 10 33' union all
select '375 17' union all
select 'PERSON' union all
select 'CARD 30 PAID'
) as t
) as t

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 07:18:35

quote:

Originally posted by RyanRandall

'PERSO'?

select data, substring(numbers,1,patindex('%[ ]%',numbers+' ')-1) as new_data from
(
select data,substring(data,patindex('%[0-9]%',data),len(data)+1) as numbers from
(
select 'CHQ: 10 780213' as data union all
select '************** 1 53' union all
select 'AMEX T.C.STORE 9' union all
select 'AMEX T.C.STORE 10 33' union all
select '375 17' union all
select 'PERSON' union all
select 'CARD 30 PAID'
) as t
) as t

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Good catch

Nice to see you here after a long time

Seems you want to visit here once in three months

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-04 : 07:41:08

Thanks Madhivanan

It's good to re-sharpen your skills every now and again, and this is my favo[u]rite way of doing it

I see you and peso are still relentless!

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 07:46:22

quote:

It's good to re-sharpen your skills every now and again, and this is my favo[u]rite way of doing it

Yes

quote:

I see you and peso are still relentless!

Thanks

Madhivanan

Failing to plan is Planning to fail

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-04 : 10:26:08

THANKS!

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-07 : 12:12:13

Actually, this has not worked entirely as planned.

I ran the following:

SELECT [DESCRIPTION], substring(numbers,1,patindex('%[ ]%',[DESCRIPTION]+' ')-1) as DESCRIPTION_NUMERIC from
(
SELECT [DESCRIPTION],substring([DESCRIPTION],patindex('%[0-9]%',[DESCRIPTION]),len([DESCRIPTION])+1) as numbers from
(
SELECT [DESCRIPTION] FROM GL_ALL_2008 ) as t
) as t

But for the following samples it did not produced the desired results:
O.P. 01423550
Result: 0142

CHQ: 7 258433
Result: 7 2

F.X.DEAL 68=80124
Result: 68=80124

It appears non-alpha characters may affect this?

Thanks

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-07 : 12:51:57

This should help a bit -

SELECT [DESCRIPTION], substring(numbers,1,patindex('%[ ]%',NUMBERS + ' ')-1) as DESCRIPTION_NUMERIC from
(
SELECT [DESCRIPTION],substring([DESCRIPTION],patindex('%[0-9]%',[DESCRIPTION]),len([DESCRIPTION])+1) as numbers from
(
SELECT [DESCRIPTION] FROM GL_ALL_2008 ) as t
) as t

The previous code was taking the first space in the entire name and using that as to choose how long the substring should be. So for P.O. 72.... It found the first space at 5, and 5 - 1 was the length for the substring, even though the substring started at the right spot. It was not told to be long enough.

Also if you want to fix your '=' sign problem replace '%[ ]%' with '%[^(0-9)]%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 01:26:51

quote:
Originally posted by DP978

This should help a bit -

SELECT [DESCRIPTION], substring(numbers,1,patindex('%[ ]%',NUMBERS + ' ')-1) as DESCRIPTION_NUMERIC from
(
SELECT [DESCRIPTION],substring([DESCRIPTION],patindex('%[0-9]%',[DESCRIPTION]),len([DESCRIPTION])+1) as numbers from
(
SELECT [DESCRIPTION] FROM GL_ALL_2008 ) as t
) as t

The previous code was taking the first space in the entire name and using that as to choose how long the substring should be. So for P.O. 72.... It found the first space at 5, and 5 - 1 was the length for the substring, even though the substring started at the right spot. It was not told to be long enough.

Also if you want to fix your '=' sign problem replace '%[ ]%' with '%[^(0-9)]%'

What is the datatype of DESCRIPTION?
See the code I posted. I initially used numbers and DESCRIPTION

Madhivanan

Failing to plan is Planning to fail

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-10 : 04:00:47

ah ok no problem, Description is varchar.

Thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources