Sql-server – Parse a string to find data between delimiters, multiple occurrences

sql serversql-server-2012string manipulation

I have a SQL Server table with one specific nvarchar column (let's assume it's 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. Their number is expected to be fixed as well.

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 the sample above.

create view v_name as
select ___ as Item1Value, ___ as Item2Value...
from details

How can this be accomplished?

Best Answer

This is a pretty boring answer, but if you are guaranteed to always have your data in that format then you can get your desired results with a bunch of CHARINDEX and SUBSTRING calls. The idea is that if you find all of the positions of the labels you can take advantage of their fixed lengths to use those positions in the arguments of the SUBSTRING calls.

By the way, it looks like you have a few typos in your sample data. I've attempted to fix them so pay special attention to my test data.

CREATE TABLE #DETAILS (TEST_STRING VARCHAR(1000));

INSERT INTO #DETAILS VALUES ('Item1: A100 ; Item2: B200; Item3:C300; Item4:D400; Item5:E500; Item6: F600600600; Item7:; Item8: H800');
INSERT INTO #DETAILS VALUES ('Item1:; Item2:; Item3:; Item4:; Item5:; Item6:; Item7:; Item8:');


SELECT
  SUBSTRING(TEST_STRING, 7, P2 - 7) ITEM1VALUE
, SUBSTRING(TEST_STRING, P2 + 8, P3 - P2 - 8) ITEM2VALUE
, SUBSTRING(TEST_STRING, P3 + 8, P4 - P3 - 8) ITEM3VALUE
, SUBSTRING(TEST_STRING, P4 + 8, P5 - P4 - 8) ITEM4VALUE
, SUBSTRING(TEST_STRING, P5 + 8, P6 - P5 - 8) ITEM5VALUE
, SUBSTRING(TEST_STRING, P6 + 8, P7 - P6 - 8) ITEM6VALUE
, SUBSTRING(TEST_STRING, P7 + 8, P8 - P7 - 8) ITEM7VALUE
, SUBSTRING(TEST_STRING, P8 + 8, 8000) ITEM8VALUE
FROM
(
    SELECT
      TEST_STRING
    , CHARINDEX('; Item2:', TEST_STRING) P2
    , CHARINDEX('; Item3:', TEST_STRING) P3
    , CHARINDEX('; Item4:', TEST_STRING) P4
    , CHARINDEX('; Item5:', TEST_STRING) P5
    , CHARINDEX('; Item6:', TEST_STRING) P6
    , CHARINDEX('; Item7:', TEST_STRING) P7
    , CHARINDEX('; Item8:', TEST_STRING) P8
    FROM #DETAILS
) t;

Results (bottom row is all nulls as expected):

╔════════╦═══════╦═══════╦═══════╦═══════╦═════════════╦═══════╦═══════╗
║ ITEM1  ║ ITEM2 ║ ITEM3 ║ ITEM4 ║ ITEM5 ║    ITEM6    ║ ITEM7 ║ ITEM8 ║
╠════════╬═══════╬═══════╬═══════╬═══════╬═════════════╬═══════╬═══════╣
║  A100  ║  B200 ║ C300  ║ D400  ║ E500  ║  F600600600 ║       ║  H800 ║
║        ║       ║       ║       ║       ║             ║       ║       ║
╚════════╩═══════╩═══════╩═══════╩═══════╩═════════════╩═══════╩═══════╝

The above code will not work if you don't always have exactly eight items and you don't always have a space between ; and the next item label (except for label 1 of course). Also note that your item values cannot contain certain values or the code will throw an error. You said that you just want to get the data between : and ; and generally delimiters should not show up in data, but here's an example of data that would cause an issue:

INSERT INTO #DETAILS VALUES ('Item1:; Item7:; Item2:; Item3:; Item4:; Item5:; Item6:; Item7:; Item8:');
Related Question