SQLTeam.com | Weblogs | Forums

How to Extract and match pattern

</ Hi , I have a column where I have a string like below. This is inside a column.

ABCC_123%XYZ_123%PQR_99%MNP_89

If you see we have a delimiter to split using % and get the value. One more condition is , we have to split with delimiter as (%) and the last characters should not be _123 and if it found we have to ignore and then again check for _123 .So in this example we can see the 3rd value does not have the _123 and we can extract that. How can we do in a Query.
My result should be PQR_99

Welcome

So pqr_99 is the 3rd delimted value.
You always want the 3rd piece and not ending by _123?

You can use string_split if order does not matter to you, or if it does, use DelimitedSplitK (from @JeffModen Google for it). To get the PQR_99 that you indicated, use the latter with TOP (1) ordered by ItemNumber column.

DECLARE @str VARCHAR(64)  = 'ABCC_123%XYZ_123%PQR_99%MNP_89';
SELECT
    value
FROM
    STRING_SPLIT(@str, '%')
WHERE
    value NOT LIKE '%[_]123';
    
SELECT TOP (1)
    Item
FROM
    dbo.DelimitedSplit8K(@str, '%')
WHERE
    Item NOT LIKE '%[_]123'
ORDER BY
    ItemNumber;
2 Likes

This kind of logic should be in the application if possible.

If you really need it the answer provided by @JamesK will be fine but when it's getting more complicated I would prefer CLR

Common Language Runtime (CLR) Overview - SQL Server | Microsoft Docs

If looking for 3rd item canbuse this

SELECT
Item
FROM
dbo.DelimitedSplit8K(@str, '%')
WHERE
Item NOT LIKE '%[_]123' and itemumber = 3

In case there is more data?

Thanks For your suggestion. I want to grab the 1st pattern of string where _123 is not present , so in this case it is PQR_99.If PQR had _123 then we could have ignored that and the last string could be the output.(MNP_89).

Is it possible to use in case or any other way because this value is coming inside one column

What you described in your first paragraph above is exactly what the query using DelimitedSplit8K will do.

If you want to use the data from a table, modify it to pick up the string from the table like shown below.

SELECT
    dsk.Item,yt.*
FROM
    YourTable AS yt
    CROSS APPLY
    (
        SELECT TOP (1)
            Item
        FROM
            dbo.DelimitedSplit8K(yt.YourColumNameHere, '%')
        WHERE
            Item NOT LIKE '%[_]123'
        ORDER BY
            ItemNumber
    ) AS dsk;

Is it possible with in column like Case. I dnt want to do join or with where clause? The reason I am saying there are a lot many columns also involved and with filter we may miss many records. So looking some answer with case

please provide your sample data with all your other columns etc
what the other devs have provided you does what you need.

This post was flagged by the community and is temporarily hidden.