string handling - 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)

string handling

Author

Topic

amit82
Starting Member

12 Posts

Posted - 2007-04-16 : 11:19:56

card payment for £120 processed today auth code
card payaent for £145 processed today, auth code
unable to process sreamline req for £415 sale not
card payment for £145 processed today auth code

consider the above 4 statements...i need only the value behind £

ideal solution :
120
145
415
145

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 11:22:10

SELECT REPLACE(@String, '£', '')

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-16 : 11:32:01

[code]
select
Num=substring(b,1,charindex(' ',b)-1)
from
(
select
b = substring(a,charindex('£',a)+1,len(a))
from
(
select a = 'card payment for £120 processed today auth code'
union all
select a = 'card payaent for £145 processed today, auth code'
union all
select a = 'unable to process sreamline req for £415 sale not'
union all
select a = 'card payment for £145 processed today auth code'
) z
) x

Results:

Num
-------------------------------------------------
120
145
415
145

(4 row(s) affected)

[/code]

CODO ERGO SUM

amit82
Starting Member

12 Posts

Posted - 2007-04-16 : 23:31:05

Thanks Michael,

amit82
Starting Member

12 Posts

Posted - 2007-04-17 : 00:43:07

Thanks A lot buddy..
But there are 1000 odd statements wherein some where there is a space between £ and the value for eg..
card payment for £ 120 processed today

the above code does not work here

select
Num=substring(b,1,charindex(' ',b)-1)
from
(
select
b = substring(a,charindex('£',a)+1,len(a))

from
(
select a = 'card payment for £ 120 processed today auth code'
union all
select a = 'card payaent for £145 processed today, auth code'
union all
select a = 'unable to process sreamline req for £415 sale not'
union all
select a = 'card payment for £145 processed today auth code'
) z
) x

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 01:21:59

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

SELECT dbo.fnFilterString(@s, '[0-9]', '')

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-17 : 07:15:34

or
In MVJ's query use this
b = substring(a,charindex('£ ',a)+1,len(a))

Madhivanan

Failing to plan is Planning to fail

amit82
Starting Member

12 Posts

Posted - 2007-04-17 : 07:29:58

or
In MVJ's query use this
b = substring(a,charindex('£ ',a)+1,len(a))

Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan,
Thanks For your support....
But as i said earlier...
there are 1000 statement where in certain statements there is no space between £ and amount (£145) and in some there are (£ 145)

I remodify my question'
card payment for £ 120 processed today auth code
card payaent for £145 processed today, auth code
unable to process sreamline req for £ 415 sale not
card payment for £145 processed today auth code

ideal solution
120
145
415
145

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 08:28:59

What about this line
card payaent for 145£ processed today, auth code

is that possible?

Peter Larsson
Helsingborg, Sweden

amit82
Starting Member

12 Posts

Posted - 2007-04-17 : 08:30:44

no peter,
that is not possible..
Sorry If i am distrubing you too much....

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 08:37:38

In MVJ's query, make this change:

Select
Num=substring(b,1,charindex(' ',b)-1) 
from
	(
		select
		b = ltrim(substring(a,charindex('£',a)+1,len(a)))
		from
			(
			select a = 'card payment for £ 120 processed today auth code'
			union all
			select a = 'card payaent for £145 processed today, auth code'
			union all
			select a = 'unable to process sreamline req for £415 sale not'
			union all
			select a = 'card payment for £145 processed today auth code'
			) z
	) x

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 08:43:54

When you find some time, look at the suggestion I made 04/17/2007 : 01:21:59

Peter Larsson
Helsingborg, Sweden

amit82
Starting Member

12 Posts

Posted - 2007-04-17 : 09:11:41

You guys have made my day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources