March 6, 2017 at 10:34 am
Hi,
I want to retrieve the ID value from CustomFields and insert into CustomID and get the Value and insert into CustomValue . When CustomFields is [] then no action needed .
CREATE TABLE #Staging
(
ID NVARCHAR(10),
CustomFields NVARCHAR(100),
CustomId NVARCHAR(100),
CustomValue NVARCHAR(100)
)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID1','[{"id":"IDXNA","value":"Organization/Roles"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID2','[{"id":"IDXNA","value":"Project Management"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID3','[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID4','[]',NULL,NULL)
SELECT * FROM #Staging
DROP TABLE #Staging
--Desired results
SELECT 'ID1','[{"id":"IDXNA","value":"Organization/Roles"}]','IDXNA' AS CustomID,'Organization/Roles' AS CustomValue
UNION
SELECT 'ID2','[{"id":"IDXNA","value":"Project Management"}]','IDXNA' AS CustomID,'Project Management' AS CustomValue
UNION
SELECT 'ID3','[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]','IHN3K' AS CustomID,'true' AS CustomValue
UNION
SELECT 'ID3','[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]','IHN54' AS CustomID,'true' AS CustomValue
UNION
SELECT 'ID3','[]','' AS CustomID,'' AS CustomValue
Thanks,
PSB
March 6, 2017 at 11:23 am
Best option, stop getting values like that.
Alternative option, try the following code:
SELECT ID, CustomFields,
ISNULL(SUBSTRING( s.Item, 7, NULLIF(CHARINDEX('"', s.Item, 7), 0)-7), '') AS CustomId,
ISNULL(SUBSTRING( s.Item, y.ValueStart + 9, NULLIF(CHARINDEX('"', S.Item, y.ValueStart + 9) - y.ValueStart,0) - 9), '') AS CustomValue
FROM #Staging
CROSS APPLY (SELECT REPLACE( REPLACE( REPLACE( CustomFields, '[{', ''), '}]', ''), '},{', CHAR(7)) AS CleanString)x
CROSS APPLY dbo.DelimitedSplit8K_LEAD(x.CleanString, CHAR(7)) s
CROSS APPLY (SELECT CHARINDEX( '"value":', s.Item) AS ValueStart) y
Find the splitting function from this article: http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/
March 6, 2017 at 11:47 am
PSB - Monday, March 6, 2017 10:34 AMHi,I want to retrieve the ID value from CustomFields and insert into CustomID and get the Value and insert into CustomValue . When CustomFields is [] then no action needed .
CREATE TABLE #Staging
(
ID NVARCHAR(10),
CustomFields NVARCHAR(100),
CustomId NVARCHAR(100),
CustomValue NVARCHAR(100)
)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID1','[{"id":"IDXNA","value":"Organization/Roles"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID2','[{"id":"IDXNA","value":"Project Management"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID3','[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID4','[]',NULL,NULL)
SELECT * FROM #Staging
DROP TABLE #Staging
--Desired results
SELECT 'ID1','[{"id":"IDXNA","value":"Organization/Roles"}]','IDXNA' AS CustomID,'Organization/Roles' AS CustomValue
UNION
SELECT 'ID2','[{"id":"IDXNA","value":"Project Management"}]','IDXNA' AS CustomID,'Project Management' AS CustomValue
UNION
SELECT 'ID3','[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]','IHN3K' AS CustomID,'true' AS CustomValue
UNION
SELECT 'ID3','[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]','IHN54' AS CustomID,'true' AS CustomValue
UNION
SELECT 'ID3','[]','' AS CustomID,'' AS CustomValue
Thanks,
PSB
Have you tried the json functions in SQL Server 2016? Should make this a breeze:
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 6, 2017 at 1:20 pm
Thanks that worked perfectly .
March 6, 2017 at 1:21 pm
That's a very nice thing to know . JSON in SQL 2016 . Will definitely try it out .
Thanks,
PSB
March 6, 2017 at 3:52 pm
Here's a solution that doesn't require the JSON functions and doesn't use REPLACE, which can be slow with large volumes of records:CREATE TABLE #Staging (
ID NVARCHAR(10),
CustomFields NVARCHAR(100),
CustomID NVARCHAR(100),
CustomValue NVARCHAR(100)
);
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID1','[{"id":"IDXNA","value":"Organization/Roles"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID2','[{"id":"IDXNA","value":"Project Management"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID3','[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]',NULL,NULL)
INSERT INTO #Staging ( ID,CustomFields,CustomID,CustomValue ) VALUES ( 'ID4','[]',NULL,NULL)
SELECT *
FROM #Staging;
SELECT ST.ID, ST.CustomFields,
ROW_NUMBER() OVER(PARTITION BY ST.ID ORDER BY S.ItemNumber, S2.ItemNumber, S3.ItemNumber) AS ROWNUM,
NTILE(2) OVER(PARTITION BY ST.ID ORDER BY S.ItemNumber, S2.ItemNumber, S3.ItemNumber) AS GROUPNUM,
SUBSTRING(S3.Item, 2, CHARINDEX('"', S3.Item, 2)-2) AS ELEMENT
INTO #SPLITS
FROM #Staging AS ST
CROSS APPLY dbo.DelimitedSplit8K_LEAD(ST.CustomFields, '{') AS S
CROSS APPLY dbo.DelimitedSplit8K_LEAD(S.Item, ',') AS S2
CROSS APPLY dbo.DelimitedSplit8K_LEAD(S2.Item, ':') AS S3
WHERE S.Item NOT IN ('[', '[]')
AND S2.Item <> ''
ORDER BY ST.ID, S.ItemNumber, S2.ItemNumber, S3.ItemNumber;
WITH ELEMENTS AS (
SELECT *
FROM (
SELECT S.ID, S.CustomFields,
S.ELEMENT, LEAD(S.ELEMENT, 1) OVER(PARTITION BY S.ID ORDER BY S.ROWNUM) AS NEXT_ELEMENT,
S.ROWNUM,
CASE
WHEN S.ROWNUM > 4 THEN S.GROUPNUM
ELSE 1
END AS GROUPNUM
FROM #SPLITS AS S
) AS X
WHERE X.ROWNUM % 2 = 1
)
SELECT ST.ID,
ST.CustomFields,
MAX(CASE E.ELEMENT WHEN 'id' THEN E.NEXT_ELEMENT ELSE ST.CustomID END) AS CustomID,
MAX(CASE E.ELEMENT WHEN 'value' THEN E.NEXT_ELEMENT ELSE ST.CustomValue END) AS CustomValue
FROM #Staging AS ST
LEFT OUTER JOIN ELEMENTS AS E
ON ST.ID = E.ID
GROUP BY ST.ID, ST.CustomFields, E.GROUPNUM
ORDER BY ST.ID;
DROP TABLE #SPLITS;
DROP TABLE #Staging;
--Desired results
SELECT 'ID1' AS ID,'[{"id":"IDXNA","value":"Organization/Roles"}]' AS CustomFields, 'IDXNA' AS CustomID, 'Organization/Roles' AS CustomValue
UNION
SELECT 'ID2', '[{"id":"IDXNA","value":"Project Management"}]', 'IDXNA' AS CustomID,'Project Management' AS CustomValue
UNION
SELECT 'ID3', '[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]', 'IHN3K' AS CustomID, 'true' AS CustomValue
UNION
SELECT 'ID3', '[{"id":"IHN3K","value":"true"},{"id":"IHN54","value":"true"}]', 'IHN54' AS CustomID, 'true' AS CustomValue
UNION
SELECT 'ID4', '[]', '' AS CustomID, '' AS CustomValue
FYI, your original inserts are in this code, as are the expected results. The original expected results appeared to have a typo in that the last record in them used ID3, when the table inserts at the beginning used ID4. Thus I updated the expected results code to match.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply