Extract data from a long string

ll1-2690 80 Reputation points
2023-05-15T02:14:24.8766667+00:00

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

User's image

I need to filter out the symbolName data from it.

Any help is greatly appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,593 questions
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-05-15T02:34:42.13+00:00

    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:

    User's image

    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.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,911 Reputation points
    2023-05-15T02:50:41.17+00:00

    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%';
    
    0 comments No comments