Retrieve value from a string

• PSB

  SSChampion

  Points: 13195

  More actions

  March 6, 2017 at 10:34 am

  #319320

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 6, 2017 at 11:23 am

  #1931887

  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/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  March 6, 2017 at 11:47 am

  #1931892

  PSB - Monday, March 6, 2017 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

  Have you tried the json functions in SQL Server 2016? Should make this a breeze:

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• PSB

  SSChampion

  Points: 13195

  More actions

  March 6, 2017 at 1:20 pm

  #1931923

  Thanks that worked perfectly .

• PSB

  SSChampion

  Points: 13195

  More actions

  March 6, 2017 at 1:21 pm

  #1931925

  That's a very nice thing to know . JSON in SQL 2016 . Will definitely try it out .

  Thanks,
  PSB

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 6, 2017 at 3:52 pm

  #1931955

  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)