January 26, 2017 at 7:51 pm
I have a SQL Server table with one specific nvarchar column (lets assume its called "details") having data like below:
Item1: A100 ; Item2: B200; Item3:C300: Item4:D400; Item5:E500; Item6: F600600600; Item7: ;Item8: H800
I am looking to parse this specific column "details" in the table and store each item value in individual columns through a SQL view so that I can use it for querying individual elements/reporting.
The item labels/descriptions "item1", "item2" etc will remain the same, so I may not need to extract that.
However I need to extract each item value between the : and ; and have them assigned to Item1value, Item2value in my view And sometimes some values may be simply null like shown in sample above.
create view v_name as
select _ as Item1Value, _ as Item2Value... from details
How can this be accomplished? (extracting the individual item values)
Thank you for any input.
January 27, 2017 at 1:02 am
Quick suggestion towards a solution based on DelimitedSplit8K
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @DETAILS VARCHAR(1000) = 'Item1: A100 ; Item2: B200; Item3:C300; Item4:D400; Item5:E500; Item6: F600600600; Item7: ;Item8: H800';
SELECT
XPAIR.ItemNumber
,MAX(CASE WHEN VPAIR.ItemNumber = 1 THEN LTRIM(VPAIR.Item) END) AS P_NAME
,MAX(CASE WHEN VPAIR.ItemNumber = 2 THEN LTRIM(VPAIR.Item) END) AS P_VALUE
FROM dbo.DelimitedSplit8K(@DETAILS ,CHAR(59)) XPAIR
CROSS APPLY dbo.DelimitedSplit8K(XPAIR.Item,CHAR(58)) VPAIR
GROUP BY XPAIR.ItemNumber;
Output
January 27, 2017 at 11:16 am
forumuser15 - Thursday, January 26, 2017 7:51 PMI have a SQL Server table with one specific nvarchar column (lets assume its called "details") having data like below:Item1: A100 ; Item2: B200; Item3:C300: Item4:D400; Item5:E500; Item6: F600600600; Item7: ;Item8: H800
I am looking to parse this specific column "details" in the table and store each item value in individual columns through a SQL view so that I can use it for querying individual elements/reporting.
The item labels/descriptions "item1", "item2" etc will remain the same, so I may not need to extract that.
However I need to extract each item value between the : and ; and have them assigned to Item1value, Item2value in my view And sometimes some values may be simply null like shown in sample above.
create view v_name as
select _ as Item1Value, _ as Item2Value... from detailsHow can this be accomplished? (extracting the individual item values)
Thank you for any input.
Try:
DECLARE
@tbl table (Id int IDENTITY, Details varchar(8000) );
INSERT @tbl (Details) VALUES ( 'Item1: A100 ; Item2: B200; Item3:C300; Item4:BigGrin400; Item5:E500; Item6: F600600600; Item7: ;Item8: H800' );
WITH
n10 (n) AS
( SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(n) ),
n100 (n) AS
( SELECT a.n FROM n10 a CROSS JOIN n10 b ),
n10000 (n) AS
( SELECT a.n FROM n100 a CROSS JOIN n100 b ),
n100000000 (n) AS
( SELECT a.n FROM n100 a CROSS JOIN n100 b ),
Numbers(Number) AS
( SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) FROM n100000000 ),
Split
(idx, word) AS
( SELECT
Row_Number() OVER (ORDER BY (SELECT NULL) )
, LTrim(RTRIM(SUBSTRING(Replace(t.Details, ':', ';'), Numbers.Number, CHARINDEX(';', Replace(t.Details, ':', ';')+';', Numbers.Number+1)-Numbers.Number)))
FROM
Numbers
CROSS APPLY
@tbl t
WHERE
Substring(';'+Replace(t.Details, ':', ';'), Numbers.Number, 1) = ';'
AND Numbers.Number BETWEEN 1 AND Len(Replace(t.Details, ':', ';')) )
SELECT s1.word Label
, s2.word StringValue
FROM
Split s1
JOIN
Split s2
ON s2.idx = s1.idx+1
AND s1.idx % 2 = 1
AND s2.idx % 2 = 0
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply