SQL Query Split

Question

SQL Query Split

Handian Sudianto 6,966

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.

User's image

Yitzhak Khabinsky 27,106 Reputation points

2024-07-19T12:20:00.6966667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

All within the question as text, no images.

2 answers

Your answer

Yitzhak Khabinsky 27,106 Reputation points

2024-07-19T12:20:00.6966667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

All within the question as text, no images.

Answer 1

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

Answer 2

Erland Sommarskog 133.9K MVP Volunteer Moderator

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.

0 comments

Share via

SQL Query Split

2 answers

Your answer