By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @SomeDelimitedString VARCHAR(100)='"par{}t1"|1|20190920';
DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');
SELECT @SomeDelimitedString AS TheOriginal
,@JsonArray AS TransformedToJSON
,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
,TheSecondFragment INT '$[1]'
,TheThirdFragment DATE '$[2]') ValuesFromTheArray
Msg 13609 Level 16 State 4 Line 5
JSON text is not properly formatted. Unexpected character 'p' is found at position 4.
DECLARE @SomeDelimitedString VARCHAR(100)='"par{}t1"|1|20190920';
DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(STRING_ESCAPE(@SomeDelimitedString, 'JSON'),'|','","'),'"]]');
SELECT @SomeDelimitedString AS TheOriginal
,@JsonArray AS TransformedToJSON
,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
,TheSecondFragment INT '$[1]'
,TheThirdFragment DATE '$[2]') ValuesFromTheArray
| TheOriginal | TransformedToJSON | TheFirstFragment | TheSecondFragment | TheThirdFragment |
|---|---|---|---|---|
| "par{}t1"|1|20190920 | [[""par{}t1"","1","20190920"]] | "par{}t1" | 1 | 2019-09-20 |