SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to split text to Columns based on Delimiters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srinisql
Starting Member

9 Posts

Posted - 03/17/2009 :  01:07:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 03/17/2009 :  01:24:38  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote


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...

Edited by - senthil_nagore on 03/17/2009 01:26:29
Go to Top of Page

srinisql
Starting Member

9 Posts

Posted - 03/17/2009 :  01:36:38  Show Profile  Reply with Quote
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

Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 03/17/2009 :  01:37:29  Show Profile  Reply with Quote
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)
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
350 Posts

Posted - 03/17/2009 :  01:59:07  Show Profile  Reply with Quote
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
Go to Top of Page

srinisql
Starting Member

9 Posts

Posted - 03/17/2009 :  02:11:04  Show Profile  Reply with Quote
Thanks your Reply. Let me try. Thanks for you and senthil for dedicating your valuable time for the reply
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 03/17/2009 :  02:22:25  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000