String manipulation help - 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 manipulation help

Author

Topic

Antonio
Posting Yak Master

168 Posts

Posted - 2006-11-07 : 10:13:50

Greetings SQL friends,

I have a column which contains the following data :

1ST LC WED 7 BALL
49S WED SIX BALL LUNCH
49S WEDS SIX BALL TEA
1ST LC WEDS SIX BALL
1ST IL SIX/6 SATURDAY 16/03
2ND IL SIX/6 SATURDAY 16/03
1ST IL SIX/6 WEDNESDAY 10/04
1ST IL SEVEN/7 WEDNESDAY 10/04
1ST IL SIX/6 SATURDAY 06/07

what I want to acheive is this :

1ST LC SAT 7 BALL
1ST LC WED 7 BALL
49S SAT TEA
BOOSTER TUES LUNCH
1ST LC WED 6 BALL
1ST LC WED 7 BALL
49S WED SIX BALL LUNCH
49S WEDS SIX BALL TEA
1ST LC WEDS SIX BALL
1ST IL SIX/6 SATURDAY
2ND IL SIX/6 SATURDAY
1ST IL SIX/6 WEDNESDAY
1ST IL SEVEN/7 WEDNESDAY
1ST IL SIX/6 SATURDAY
2ND IL SIX/6 SATURDAY
1ST IL SIX/6 SATURDAY
2ND IL SIX/6 SATURDAY
49 SIX/6 TEA SATURDAY
2ND IL SIX/6 SATURDAY

so really it's chopping the date off the end But not all the fields contain dates which is why I am struggling with this.

Some sample data :


create table #test (betting_opportunity_name varchar(100))

insert into #test
select '1ST LC SAT 7 BALL' union all
select '1ST LC WED 7 BALL' union all
select '49S SAT TEA' union all
select 'BOOSTER TUES LUNCH' union all
select '1ST LC WED 6 BALL' union all
select '1ST LC WED 7 BALL' union all
select '49S WED SIX BALL LUNCH' union all
select '49S WEDS SIX BALL TEA' union all
select '1ST LC WEDS SIX BALL' union all
select '1ST IL SIX/6 SATURDAY 16/03' union all
select '2ND IL SIX/6 SATURDAY 16/03' union all
select '1ST IL SIX/6 WEDNESDAY 10/04' union all
select '1ST IL SEVEN/7 WEDNESDAY 10/04' union all
select '1ST IL SIX/6 SATURDAY 06/07' union all
select '2ND IL SIX/6 SATURDAY 06/07' union all
select '1ST IL SIX/6 SATURDAY 13/07' union all
select '2ND IL SIX/6 SATURDAY 13/07' union all
select '49 SIX/6 TEA SATURDAY 13/07' union all
select '2ND IL SIX/6 SATURDAY 20/07'

Thanks for your help in advance.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 10:22:29

[code]select betting_opportunity_name,
left(betting_opportunity_name, case when patindex('%[0-9][0-9]/[0-9][0-9]%', betting_opportunity_name)=0 then len(betting_opportunity_name) else patindex('%[0-9][0-9]/[0-9][0-9]%', betting_opportunity_name)-1 end)
from #test[/code]

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 10:34:33

May need to drop the trialing "%" otherwise embedded dates may not work very well, e.g.:

union all
select 'xxx 20/07 yyy'

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 10:38:40

You're right! Is was my first draft. Trial and error you know

select	betting_opportunity_name,
	left(betting_opportunity_name, case when patindex('%[0-9][0-9]/[0-9][0-9]', betting_opportunity_name)=0 then len(betting_opportunity_name) else patindex('%[0-9][0-9]/[0-9][0-9]', betting_opportunity_name)-1 end)
from	#test

Peter Larsson
Helsingborg, Sweden

EDIT: Removed second trailing %-sign.

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 10:44:49

Do you want the "%" at the end of the first patindex?

With it in place I get:

Server: Msg 536, Level 16, State 3, Line 29
Invalid length parameter passed to the substring function.

I wonder if this avoids two calls to patindex (and might therefore be faster):


select	patindex('%[0-9][0-9]/[0-9][0-9]', betting_opportunity_name), betting_opportunity_name,
	left(betting_opportunity_name, 
		Coalesce(
			NullIf(
				patindex('%[0-9][0-9]/[0-9][0-9]', betting_opportunity_name)-1,
				-1), 
			len(betting_opportunity_name)
			)
		)
from	#test

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 10:52:38

Derived table?

select	q.betting_opportunity_name,
	left(q.betting_opportunity_name, case when q.position = 0 then q.length else q.position - 1 end)
from	(
		select	betting_opportunity_name,
			patindex('% [0-9][0-9]/[0-9][0-9]', betting_opportunity_name) position,
			len(betting_opportunity_name) length
		from	#test
	) q

Peter Larsson
Helsingborg, Sweden

Antonio
Posting Yak Master

168 Posts

Posted - 2006-11-07 : 11:10:50

Thanks to you both. I appreciate your help. Must learn more about reg exp.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources