Take a look at this short article on my web site: https://www.sommarskog.se/arrays-in-sql.html Particularly, chapter four is of interest to you.
SQL Query Split
Handian Sudianto
5,001
Reputation points
Hello,
I have query with result like below pic, now i want to separate the value to 5 columns based on value of al, av, date, int and KB so the expected final result is 07/18/2024 17:47:35 , 1.415.174.0, 07/15/2024 00:00:00, False and 5040430.
And also want to ignore the record with no contains above parameters.
2 answers
Sort by: Most helpful
-
-
LiHongMSFT-4306 27,426 Reputation points
2024-07-19T09:34:12.9366667+00:00 Try this:
DECLARE @TBL TABLE(MultiValueMessages VARCHAR(100)) INSERT INTO @TBL VALUES ('al:07/18/2024 17:47:35, av:1.415.174.0, Date:07/15/2024 00:00:00, int:False, KB:5040430') ;WITH CTE AS ( SELECT *,'["'+ REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(MultiValueMessages,' ',''),'al:',''),'av:',''),'Date:',''),'int:',''),'KB:',''),',','","') + '"] ' jsCol FROM @TBL WHERE MultiValueMessages LIKE 'al%' ) SELECT DISTINCT JSON_VALUE(jsCol, '$[0]') AS al ,JSON_VALUE(jsCol, '$[1]') AS av ,JSON_VALUE(jsCol, '$[2]') AS Date ,JSON_VALUE(jsCol, '$[3]') AS int ,JSON_VALUE(jsCol, '$[4]') AS KB FROM CTE CROSS APPLY OPENJSON(jsCol) AS j
Best regards,
Cosmog
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".