SQLTeam.com | Weblogs | Forums

How to Extract and match pattern

#1

</ 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

#2

Welcome

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

#3

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
#4

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

#5

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?

#6

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

#7

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;
#8

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

#9

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

#10

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