14,494 questions
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
6,096
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.
SQL Server Other
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Yitzhak Khabinsky 26,586 Reputation points2024-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.
Sign in to comment
2 answers
Sort by: Most helpful
-
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator2024-07-19T08:15:45.9+00:00 -
LiHongMSFT-4306 31,566 Reputation points2024-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 jBest 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".