How can a split a string in SQL

Question

Hi!! I have a column that have lines that look like these:

LINE1: {value=vneobbnlri, id=123}, {value=ajfheofbks, id = 456}, {value=malualves, id = 678}....
LINE 2: {value=fhegnbegiervnrte uigel, id=123}, {value=ihefbgiuergbi, id = 456}, {value=malualve123, id = 678}

I want a query that my output would be the string that comes BEFORE the id 678. In the first line malualves and in the second malualves123 I´ve used substring and charindex, but i dont know how to separate only that string.

Answer 1

Hi @Maria Luiza Alves Geraldo ,

Your data resembles JSON. So after some string massaging we can convert it into JSON and query it.

The proposed solution is working starting from SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, jsondata NVARCHAR(MAX));  
INSERT INTO @tbl (jsondata) VALUES  
(N'{value=vneobbnlri, id=123}, {value=ajfheofbks, id = 456}, {value=malualves, id = 678}'),  
(N'{value=fhegnbegiervnrte uigel, id=123}, {value=ihefbgiuergbi, id = 456}, {value=malualve123, id = 678}');  
-- DDL and sample data population, end  
  
;WITH rs AS  
(  
 SELECT ID  
 , '[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(jsondata  
 , '{', '{"')  
 , ' = ', '=')  
 , '=', '":"')  
 , '}', '"}')  
 , ', ', '","')  
 , '}","{', '},{') + ']' AS jsondata  
 FROM @tbl  
)  
SELECT rs.ID, report.*  
FROM rs  
 CROSS APPLY OPENJSON(jsondata)  
WITH   
(  
    [id] bigint '$.id'  
    , [value] varchar(100) '$.value'  
) AS report  
WHERE report.id = 678;  

Output

+----+-----+-------------+  
| ID | id  |    value    |  
+----+-----+-------------+  
|  1 | 678 | malualves   |  
|  2 | 678 | malualve123 |  
+----+-----+-------------+  

Answer 2

First use a function to split the list into table format with }, { as the delimiter. You find such functions here: http://www.sommarskog.se/arrays-in-sql.html. As input to the function pass substring(col, 2, len(col)-2) to get rid of the first and last braces.

In these rows you want them that matches '678%' and grab the value with substring(str, 7, charindex(',', str)-7).

I did not test this, and most likely you will need to fine-tune. Some of the numbers may be off by 1 or so.

Answer 3

If your SQL server is 2016 or higher, try this:

--DECLARE @s nvarchar(max) = '{value=vneobbnlri, id=123}, {value=ajfheofbks, id = 456}, {value=malualves, id = 678}';
DECLARE @s nvarchar(max) = '{value=fhegnbegiervnrte uigel, id=123}, {value=ihefbgiuergbi, id = 456}, {value=malualve123, id = 678}';
SELECT SUBSTRING(value, LEN('value=') + 1, LEN(value) - LEN('value=') - LEN(', id = 678}'))
FROM STRING_SPLIT(REPLACE(@s, '}, {', '|'), '|')
WHERE value LIKE '%id = 678%';

Answer 4

Hi @Maria Luiza Alves Geraldo ,

Welcome to the Microsoft TSQL Q&A Forum!

Please try:

    declare @yourtable table(string char(255))  
      
    insert into @yourtable values('{value=vneobbnlri, id=123}, {value=ajfheofbks, id = 456}, {value=malualves, id = 678}'),  
    ('{value=fhegnbegiervnrte uigel, id=123}, {value=ihefbgiuergbi, id = 456}, {value=malualve123, id = 678}')  
      
    ;with cte  
    as(select reverse(left(string,charindex('id = 678',string)-3)) re from @yourtable)  
    select reverse(left(re,charindex('=',re)-1))   
    from cte  

Output:

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.