my turn to play substring - 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)

my turn to play substring

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-27 : 03:33:22

lol...finally my turn to play substring...
in case i got these data

raw data
hi|aaa|what are you doing|bbb|imagine what you can done|ccc|haha|ddd|
hi|aaa|wahahahah|ccc|what else|eee|
harlo|aaa|this|bbb|is|ccc|totally|confusing|eee|need|hhh|help|zzz|!!!

now those value between '|aaa|' or etc is a delimiter, however the delimiter does not always in the totally correct ordering..so how do i separate those value before, between and after the delimiter into columns?

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-27 : 03:42:46

One of the methods


declare @s varchar(1000)
set @s='hi|aaa|what are you doing|bbb|imagine what you can done|ccc|haha|ddd|'

declare @t table(id int identity(1,1),data varchar(1000))

while charindex('|',@s)>0
begin
	insert into @t(data)
	select substring(@s,1,charindex('|',@s)-1)
	set @s=substring(@s,charindex('|',@s)+1,len(@s))
end


select data from @t
where id%2=1

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-27 : 03:45:23

erm...madhi...is divide them into column..not rows...you can assume '|aaa|' is a delimiter that divide a full string into column

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-27 : 03:50:02

quote:
Originally posted by waterduck

erm...madhi...is divide them into column..not rows...you can assume '|aaa|' is a delimiter that divide a full string into column

Hope can help...but advise to wait pros with confirmation...

See if you can make use of this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/11/splitting-delimited-data-to-columns-set-based-approach.aspx

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-27 : 03:57:31

ahhaha thx madhi...i go try first...

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-28 : 03:16:32

Are you able to solve it?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-28 : 03:29:47

kinda busy at my front end thingy...wait till i got time to solve it....sorry madhi

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-28 : 03:34:26

quote:
Originally posted by waterduck

kinda busy at my front end thingy...wait till i got time to solve it....sorry madhi

Hope can help...but advise to wait pros with confirmation...

Ok Fine. Which front end are you working on?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources