Extract data from a long string

Question

Hello.

We have a very long string from which we want to find a query to extract the data we need. In the following example, I have only intercepted part of the string.

linkName:jlcress.com,symbolName:kga-bdsv-d,imageName:picture/215a,symbolName:abzs-mud-ad,imageName:picture/kahi-qd,linkName:ljsaih.cn,symbolName:avs-4ac-3,symbolName:ojs-mas-nug,imageName:picture/fjq

The desired output

I need to filter out the symbolName data from it.

Any help is greatly appreciated.

Answer 1

Hi @lug-1942

You can try this query.

create table test(string varchar(max));
insert into test values ('linkName:jlcress.com,symbolName:kga-bdsv-d,imageName:picture/215a,symbolName:abzs-mud-ad,imageName:picture/kahi-qd,linkName:ljsaih.cn,symbolName:avs-4ac-3,symbolName:ojs-mas-nug,imageName:picture/fjq');
select * from test;
;with T1 as(
  select value from test cross apply string_split(string,',')
),T2 as(
  select * from T1 where value like 'symbolName%'
),T3 as(
  select trim('symbolName' from value) as symbolName from T2
),T4 as(
  select trim(':' from symbolName) as symbolName from T3)
select row_number()over(order by symbolName) as ID,symbolName from T4;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

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.

Answer 2

HI @lug-1942,

Please try the following solution. It will work starting from MS SQL Server 2016 onwards.

-- DDL and data population, start
DECLARE @tbl table (input VARCHAR(MAX));
INSERT @tbl VALUES 
('linkName:jlcress.com,symbolName:kga-bdsv-d,imageName:picture/215a,symbolName:abzs-mud-ad,imageName:picture/kahi-qd,linkName:ljsaih.cn,symbolName:avs-4ac-3,symbolName:ojs-mas-nug,imageName:picture/fjq');
-- DDL and data population, end

SELECT ID = ROW_NUMBER() OVER (ORDER BY @@SPID)
	, symbolName = STUFF(value, 1, LEN('symbolName:'), '')
FROM @tbl 
	CROSS APPLY STRING_SPLIT(input, ',')
WHERE value LIKE 'symbolName%';