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

April 6, 2022

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

>Solution :

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;