SQL Query Split

Question

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.

Answer 1

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.

Answer 2

Hi @Handian Sudianto

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".