Split the character - 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)

Split the character

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-06 : 08:08:15

Hi
Iam using this
Select Substring('ABC_KHL_PKL_GGG',1,CHARINDEX('_','ABC_KHL_PKL')-1)
And its giving me the result ABC which is ok

But how i will get the get 2nd, 3rd and 4th part of above string
Like result
Will be
KHL
PKL
GGG

Thanks in advance

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-06 : 08:11:45

If you always have the form <a_b_c_d> then you can have a look at the PARSENAME function
http://www.sqlteam.com/item.asp?ItemID=15044

create table #object_piece ( n int)
insert into #object_piece select 4
union all select 3
union all select 2
union all select 1

declare @string varchar(100); set @string = 'ABC_KHL_PKL_GGG'
SELECT PARSENAME(REPLACE(@string,'_','.'),n) from #object_piece

otherwise take a look at the following article:

http://www.sqlteam.com/item.asp?ItemID=2652

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-06 : 10:19:04

create table #t (n varchar(800))
insert into #t select 'A_B_QWER_GG_U_K'

select distinct
substring(n,charindex('_',n,num)+1,charindex('_',n,num+1)-charindex('_',n,num)-1)
from (select '_'+n+'_end' n from #t) t
cross join
(select num=1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9 union select 10 union select 11 union
select 12 union select 13 union select 14 union select 15 union select 16) z
where charindex('_',n,num+1)-charindex('_',n,num)-1>0

drop table #t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources