Question with SQL String

I have this code below to parse the string ''00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'' into Multiple columns.

Here is the code, but the code is splititng the columns incorrectly.

I need it to appear as

col1 col2 col3 col4 col5
00120212 pendin mod pen ria te 3/6/2007 3:51:49 pm
Can someone pl assist with this code below.

-

DECLARE @.str varchar(8000)
SET @.str = '00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'

DECLARE @.columns TABLE (
col1 varchar(8000)
,col2 varchar(8000)
,col3 varchar(8000)
)

SET @.str = LTrim(RTrim(@.str))

DECLARE @.col1 int
,@.col2 int
,@.col3 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)

INSERT INTO @.columns
VALUES (
SubString(@.str, 2, @.col1 - 2)
,SubString(@.str, @.col1 + 3, Len(@.str) - @.col2 - 4)
,SubString(@.str, @.col2 + 3, Len(@.str) - @.col3 - 1)
)

SELECT * FROM @.columns

You may find Jen Suessmeyer's Split function to be very useful for this situation.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

Please check out the link below:

http://www.sommarskog.se/arrays-in-sql.html

It contains few TVFs that can be used to split a string based on a delimiter. You can use it along with PIVOT for example to get the individual values easily like:

SELECT p.[1], p.[2], p.[3], p.[4], p.[5]

FROM split_str(@.str, '~') AS t

PIVOT (min(t.value) for t.idx in ([1], [2], [3], [4], [5])) as p

Alternatively, you can do these type of operations easily on the client side and send the values individually. This way you can use SQL for what it is supposed to do.

|||

This is by code below, Delimiter is ~. I need to split the values, into multilple field , based on the ~ being the delimiter. How do I loop throu this string, until the end of the string, and then split it one - by-one into multiple fileds? PL ADVISE?

Declare @.Str Varchar(1000),@.I Int

set @.str='0001232~PENDING~MOD PENDING~Trad Jane~3/29/2007 5:03:30 PM~0001232~PENDING~MODIFICATION PENDING~ Jane Delder~3/29/2007 5:05:06 PM~0001232~PENDING~Approved~ Mon Savy~3/29/2007 5:05:27 PM~0001232~PEND'
SET @.str = LTrim(RTrim(@.str))

DECLARE @.columns TABLE (
LoanNum varchar(8000)
,ConvertedFromStatus varchar(8000)
,ConvertedToStatus varchar(8000)
,ConvertedName varchar(8000)
,StatusChangedDate text
)

begin
Begin
DECLARE @.col1 int
,@.col2 int
,@.col3 int,
@.col4 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)
SET @.col4 = CharIndex('~', @.str, @.col3 + 1)
--print @.col1
--print @.col2
--print @.col3
--print @.col4

INSERT INTO @.columns
VALUES (
Left(@.Str, @.Col1 - 1),
SubString(@.Str, @.Col1 + 1, @.col2 - @.Col1 - 1),
SubString(@.Str, @.Col2 + 1, @.col3 - @.Col2 - 1),
SubString(@.Str, @.Col3 + 1, @.col4 - @.Col3 - 1),
SubString(@.Str, @.Col4 + 1, @.col4 )
)
End

--Select @.I = 0

End
SELECT * FROM @.columns

|||Does the Split function I previously posted for you not work properly?