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.
@MariaLuizaAlvesGeraldo-0367 We have added the Transact SQL tag to help here.
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;)
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 |
+----+-----+-------------+
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.".
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.
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.
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%';
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:
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.
13 people are following this question.
