question

MariaLuizaAlvesGeraldo-0367 avatar image
0 Votes"
MariaLuizaAlvesGeraldo-0367 asked MariaLuizaAlvesGeraldo-0367 commented

How can a split a string in SQL

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.

sql-server-transact-sqlazure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MariaLuizaAlvesGeraldo-0367 We have added the Transact SQL tag to help here.

0 Votes 0 ·

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;)

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered MariaLuizaAlvesGeraldo-0367 commented

Hi @MariaLuizaAlvesGeraldo-0367,

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 |
 +----+-----+-------------+
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks a lot!!!

That solution is almost working. The onlyne problem is that I have some ids that are bigger then varchar, like this one: 360031177893.

So SQL messages me: "The conversion of the nvarchar value '360030734853' overflowed an int column.".

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image YitzhakKhabinsky-0887 MariaLuizaAlvesGeraldo-0367 ·

Hi @MariaLuizaAlvesGeraldo-0367,

Good to hear from you.
We can easily change that data type from INT to BIGINT, and it will work.
I already updated the answer.

0 Votes 0 ·

I put bigint and now it worked!! Thanks a lot!!!!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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%';
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @MariaLuizaAlvesGeraldo-0367,

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:
79397-image.png


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.




image.png (1.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.