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
