April 6, 2016 at 6:19 am
Hi all,
Please i have this string:
"{"Sunday:"[3,4,5,]},{"Monday:"[1,6,]},{"Tuesday:"[5,6,7,]},{"Wednesday:"[6,7,]},{"Thurday:"[5,6,]},{"Friday:"[5,6,]},{"Saturday:"[1,6,7,]},"
which i have been able to separate into separate rows such as:
AnswerItem
"Sunday:"[3
4
5
"Monday:"[1
6
"Tuesday:"[5
6
7
"Wednesday:"[6
7
"Thurday:"[5
6
"Friday:"[5
6
"Saturday:"[1
6
7
using this sql
;with tmp(AnswerItem, Answer) as (
select LEFT(Answer3, CHARINDEX(',', Answer3+ ',') - 1), left((STUFF(Answer3, 1, CHARINDEX(',', Answer3+ ','), '')), NULLIF(LEN(STUFF(Answer3, 1, CHARINDEX(',', Answer3+ ','), ''))-1,-1)) From (
SELECT replace(REPLACE(answer, '{',''), ']}', '') as Answer3 FROM tbl_MediumResults
) as d
union all
select LEFT(Answer, CHARINDEX(',',Answer+',')-1),
STUFF(Answer, 1, CHARINDEX(',',Answer+','), '') From tmp
Where Answer > ''
)
Select AnswerItem From tmp where
not(AnswerItem like '')
Please how can i achieve this type of result
AnswerItem
"Sunday:"[3
"Sunday:"4
"Sunday:"5
"Monday:"[1
"Monday:"6
"Tuesday:"[5
"Tuesday:"6
"Tuesday:"7
"Wednesday:"[6
"Wednesday:"7
"Thursday:"[5
"Thursday:"6
"Friday:"[5
"Friday:"6
"Saturday:"[1
"Saturday:"6
"Saturday:"7
Thanks
Tim
April 6, 2016 at 6:51 am
Using a recursive CTE can be very expensive. I'm using an alternative to split the string which is largely explained in this article: http://qa.sqlservercentral.com/articles/Tally+Table/72993/
Ask any questions you might have.
DECLARE @tbl_MediumResults TABLE(
Answer VARCHAR(8000)
);
INSERT INTO @tbl_MediumResults
SELECT '{"Sunday:"[3,4,5,]},{"Monday:"[1,6,]},{"Tuesday:"[5,6,7,]},{"Wednesday:"[6,7,]},{"Thurday:"[5,6,]},{"Friday:"[5,6,]},{"Saturday:"[1,6,7,]},';
SELECT CASE
WHEN split2.Item LIKE '[0-9]'
THEN LEFT(split1.Item, CHARINDEX(':"', split1.Item)+1)
ELSE ''
END+split2.Item
FROM @tbl_MediumResults
CROSS APPLY( SELECT REPLACE(REPLACE(Answer, ',]},', CHAR(7)), '{', '') ) AS r(repString)
CROSS APPLY dbo.DelimitedSplit8K( repString, CHAR(7)) AS split1
CROSS APPLY dbo.DelimitedSplit8K( split1.Item, ',') AS split2
WHERE split1.Item <> '';
April 6, 2016 at 7:07 am
If you want to parse JSON into a table, Phil Factor wrote an article a couple of years ago you might find useful. https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/.
I've heard that JSON is handled natively in SQL 2016, but I haven't played around with it.
April 6, 2016 at 7:08 am
Thanks Luis for your reply,
but please look below at the error being generated:
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 8
Invalid object name 'dbo.DelimitedSplit8K'.
Please how do i overcome that?
Thanks
Tim
April 6, 2016 at 7:09 am
timotech (4/6/2016)
Thanks Luis for your reply,but please look below at the error being generated:
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 8
Invalid object name 'dbo.DelimitedSplit8K'.
Please how do i overcome that?
Thanks
Tim
Reading the article I mentioned in my post.
April 7, 2016 at 3:47 am
Reading the article I mentioned in my post.
Thanks Luis,
This is what i needed, it works now
Thanks very much
Tim
April 7, 2016 at 11:01 am
Thanks guys for your help.
Please i have another request, i've been trying to do some manipulations on the code, but i'm not getting the expected result.
Please what if i have more than one row in the given table, and they do not actually look similar, but i also want those also to be split into separate rows for example:
DECLARE @tbl_MediumResults TABLE(
Answer VARCHAR(8000)
);
INSERT INTO @tbl_MediumResults
SELECT '{"Sunday:"[3,4,5,]},{"Monday:"[1,6,]},{"Tuesday:"[5,6,7,]},{"Wednesday:"[6,7,]},{"Thurday:"[5,6,]},{"Friday:"[5,6,]},{"Saturday:"[1,6,7,]},';
Select '2,3,4,5,6'
SELECT CASE
WHEN split2.Item LIKE '[0-9]'
THEN LEFT(split1.Item, CHARINDEX(':"', split1.Item)+1)
ELSE ''
END+split2.Item
FROM @tbl_MediumResults
CROSS APPLY( SELECT REPLACE(REPLACE(Answer, ',]},', CHAR(7)), '{', '') ) AS r(repString)
CROSS APPLY dbo.DelimitedSplit8K( repString, CHAR(7)) AS split1
CROSS APPLY dbo.DelimitedSplit8K( split1.Item, ',') AS split2
WHERE split1.Item <> '';
I also want the second row to be split like the others, please what can i do?
Thanks
Tim
April 7, 2016 at 11:09 am
Is this what you're looking for?
DECLARE @tbl_MediumResults TABLE(
Answer VARCHAR(8000)
);
INSERT INTO @tbl_MediumResults
SELECT '{"Sunday:"[3,4,5,]},{"Monday:"[1,6,]},{"Tuesday:"[5,6,7,]},{"Wednesday:"[6,7,]},{"Thurday:"[5,6,]},{"Friday:"[5,6,]},{"Saturday:"[1,6,7,]},'
UNION ALL
Select '2,3,4,5,6'
SELECT Answer,
CASE
WHEN split2.Item LIKE '[0-9]' AND split1.Item LIKE '"%' --Code change in here
THEN LEFT(split1.Item, CHARINDEX(':"', split1.Item)+1)
ELSE ''
END+split2.Item
FROM @tbl_MediumResults
CROSS APPLY( SELECT REPLACE(REPLACE(Answer, ',]},', CHAR(7)), '{', '') ) AS r(repString)
CROSS APPLY dbo.DelimitedSplit8K( repString, CHAR(7)) AS split1
CROSS APPLY dbo.DelimitedSplit8K( split1.Item, ',') AS split2
WHERE split1.Item <> '';
April 7, 2016 at 11:14 am
Luis Cazares (4/7/2016)
Is this what you're looking for?
DECLARE @tbl_MediumResults TABLE(
Answer VARCHAR(8000)
);
INSERT INTO @tbl_MediumResults
SELECT '{"Sunday:"[3,4,5,]},{"Monday:"[1,6,]},{"Tuesday:"[5,6,7,]},{"Wednesday:"[6,7,]},{"Thurday:"[5,6,]},{"Friday:"[5,6,]},{"Saturday:"[1,6,7,]},'
UNION ALL
Select '2,3,4,5,6'
SELECT Answer,
CASE
WHEN split2.Item LIKE '[0-9]' AND split1.Item LIKE '"%' --Code change in here
THEN LEFT(split1.Item, CHARINDEX(':"', split1.Item)+1)
ELSE ''
END+split2.Item
FROM @tbl_MediumResults
CROSS APPLY( SELECT REPLACE(REPLACE(Answer, ',]},', CHAR(7)), '{', '') ) AS r(repString)
CROSS APPLY dbo.DelimitedSplit8K( repString, CHAR(7)) AS split1
CROSS APPLY dbo.DelimitedSplit8K( split1.Item, ',') AS split2
WHERE split1.Item <> '';
Yes!!!, thanks so much Luis
You are the man
Thanks
Tim
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply