I want to separate a string like below with id and value in SQL Server-CodePudding

DECLARE @ELEMENT1 VARCHAR(100)='REF*MK*37908-155-3**DO#0000000000#ZZ#202108161400PB3#ZZ#20210817BBBBBBB4';

and I want a answer like in below table

Identity	Value
1	REF
2	MK
3	37908-155-3
4
5	DO
6	0000000000
7	ZZ
8	202108161400PB3
9	ZZ
10	20210817BBBBBBB4

DECLARE @ELEMENT1 VARCHAR(100)='REF*MK*37908-155-3**DO#0000000000#ZZ#202108161400PB3#ZZ#20210817BBBBBBB4';
;WITH Identities AS(
    SELECT [key] / 2 AS Entry,
           MAX(CASE WHEN [key] % 2 = 0 THEN [Value] END) AS [Identity],
           MAX(CASE WHEN [key] % 2 = 1 THEN [Value] END) AS [Value]
    FROM OPENJSON(CONCAT('["',REPLACE(@ELEMENT1,'#', '","'),'"]')) OJ
    GROUP BY [key] / 2)
SELECT TOP (2)
       [Identity],
       [Value]
FROM Identities
ORDER BY Entry ASC

CodePudding user response：

You need to replace all of your delimiters, not just one of them. I additionally demonstrate the use of TRANSLATE here, in the event you have more than 2 delimiters, and therefore need to cater for them in the future:

DECLARE @ELEMENT1 VARCHAR(100)='REF*MK*37908-155-3**DO#0000000000#ZZ#202108161400PB3#ZZ#20210817BBBBBBB4';

SELECT OJ.[Key]   1 AS [Identity], --Don't use reserved keywords as aliases/object names
       [value]
FROM OPENJSON(CONCAT('["',REPLACE(TRANSLATE(@ELEMENT1,'#*','##'),'#', '","'),'"]')) OJ;

If you are on an Azure SQL Database (and hopefully SQL Server 2022 ) you can make use of STRING_SPLIT and it's ordinal parameter instead:

DECLARE @ELEMENT1 VARCHAR(100)='REF*MK*37908-155-3**DO#0000000000#ZZ#202108161400PB3#ZZ#20210817BBBBBBB4';

SELECT ordinal AS [Identity], --Don't use reserved keywords as aliases/object names
       value
FROM STRING_SPLIT(TRANSLATE(@ELEMENT1,'#*','##'),'#',1) SS;

I don't use it without the ordinal parameter, as it's documented that STRING_SPLIT does not guarantee the order in which the data is returned. Although I (and many others) have never seen such the order not be respected, the fact that it's explicitly documented to not be, and you have to rely on arbitrary ordering, could result in incorrect results which you would have no control over. Fortunately, after 6 years of complaints, Microsoft have finally added the parameter to the function.

CodePudding user response：

DECLARE @ELEMENT1 VARCHAR(100) 
                  = 'REF*MK*37908-155-3**DO#0000000000#ZZ#202108161400PB3#ZZ#20210817BBBBBBB4';


SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id
     , d2.value
FROM STRING_SPLIT(@ELEMENT1 , '*') d1
 CROSS APPLY STRING_SPLIT(d1.value , '#') d2