how to split text to Columns based on Delimiters

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)

how to split text to Columns based on Delimiters

Author

Topic

srinisql
Starting Member

9 Posts

Posted - 2009-03-17 : 01:07:30

Dear All,

Does Any Body Know how to split the text to column based on delimiter dynamically. for eg,

I have text like this
a~b~c~d@
e~f~g~h@
i~j~k~l@
which i can pass this text into function parameter, what i expect is the table return value in form of

col1 col2 col3
a b c
e f g
i j k

the column can dynamically vary in size.

Expecting your reply

Thanks in Advance

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-17 : 01:24:38

select 'a~b~c~d',substring('a~b~c~d',0,2),substring(substring('a~b~c~d',3,len('a~b~c~d')),0,2),
substring(substring('a~b~c~d',5,len('a~b~c~d')),0,2),substring(substring('a~b~c~d',7,len('a~b~c~d')),0,2)

if u can't knoe '~' position use Charindex()

select charindex('~','a~b~c~d',0)

Regards

Senthil.C
Willing to update...

srinisql
Starting Member

9 Posts

Posted - 2009-03-17 : 01:36:38

Dear Senthil,

Thanks for your reply. but the problem is there will be large values, if it is limited, we can apply your logic

The value what i passed will have to be manipulated in the loop , the value between the delimiter ~ have to be seperated as columns and the value between the delimiter @ has to be seperated as rows

Thanks in Advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-17 : 01:37:29

it will work up to 4 values only
declare @str varchar(128)
select @str ='a~b~c~d'
select @str,parsename(replace(@str,'~','.'),4),parsename(replace(@str,'~','.'),3),
parsename(replace(@str,'~','.'),2),parsename(replace(@str,'~','.'),1)

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-03-17 : 01:59:07

Maybe this could help, search for 'split text delimiter' in sql team and you will find examples. Here is one thread.

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

srinisql
Starting Member

9 Posts

Posted - 2009-03-17 : 02:11:04

Thanks your Reply. Let me try. Thanks for you and senthil for dedicating your valuable time for the reply

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-17 : 02:22:25

Here the generic code..

You can call this procedure ..

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*

scspsplit '42~1~13~2~4~42~1~<,>13~2~4~7~1~52~2~245~<,>56~1~4~2~245~<,>'

*/

ALTER Procedure scspsplit
@BacthData varchar(2000)
as

Declare @part VARCHAR(100), @remainingpart VARCHAR(100),
@balance VARCHAR(2000),@balancevar varchar(100),@var1 varchar(500),@counter int,@innercounter int

set @counter=0
while(@BacthData<>'')
BEGIN
--PRINT 'OUTER'
set @counter=@counter+1
SET @part = SUBSTRING(@BacthData,0,CHARINDEX('<,>',@BacthData))

SET @remainingpart=@part
print @part

--truncate table #setvalues
set @innercounter=0
while (@part<>'')
Begin
set @innercounter=@innercounter+1

set @var1= SUBSTRING(@part,0,CHARINDEX('~',@part))

insert into #setvalues values(@counter,@innercounter,@var1)

set @balancevar =SUBSTRING (@part,len(@var1)+2,len(@part))

SET @part=@balancevar

End

--select * from #setvalues

set @balance =SUBSTRING (@BacthData,len(@remainingpart)+4,len(@BacthData))

set @BacthData=@balance

END

--select * from #setvalues

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards

Senthil.C
Willing to update...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources