CODEWITHSUNDEEP
sqlsql-serverstringsplit
Red
Red

Reputation: 47

Splitting string into columns in SQL Server

I'm having some difficulty with splitting the following string:

"greeting1": "hi", "greeting2": "hello", "greeting3": "hey"

...into my desired output as separate columns:

+------------+------------+-----------+
| greeting1  | greeting2  | greeting3 | 
+------------+------------+-----------+
|   hi       |  hello     |   hey     |               
+------------+------------+-----------+

FYI- I am using SQL Server, the maximum number of greetings is five, and I know of a STRING_SPLIT function that I could use but I don't really know how to use it- also considering the amount of quotation marks and colons in my string. Any feedback or suggestions would be super helpful! Thank you. :)

Upvotes: 0

Views: 567

Answers (1)

Zhorov
Zhorov

Reputation: 29943

If you are using SQL Server 2016+, you may parse the input text as JSON using OPENJSON() and explicit schema (the WITH clause). If the maximun number of greetings is five, you need to use the appropriate columns definitions:

DECLARE @text varchar(1000) = '"greeting1": "hi", "greeting2": "hello", "greeting3": "hey"'

SELECT *
FROM OPENJSON(CONCAT('{', @text, '}')) WITH (
   greeting1 varchar(1000) '$.greeting1',
   greeting2 varchar(1000) '$.greeting2',
   greeting3 varchar(1000) '$.greeting3',
   greeting4 varchar(1000) '$.greeting4',
   greeting5 varchar(1000) '$.greeting5'
)

Result:

greeting1 greeting2 greeting3 greeting4 greeting5
-------------------------------------------------
hi        hello     hey

Upvotes: 6

Related Questions