CODEWITHSUNDEEP
sqlsql-servert-sql
breezymri
breezymri

Reputation: 4343

Search for pattern in part of the string

I need to search for a pattern in a string field which contains an email and an additional comment. An example is the following:

Dear XXX: We are sorry to inform you that your 
application has been put on hold because YYY. 

Additional comments: this application was not held for ZZZ.

The pattern I am looking for is "ZZZ" in the email body. But not in the comments. If I just do field like 'ZZZ', I will be picking up this record, which is not what I want. Is there a way to carry out the search accurately in this case?

Note that a subset of the records have the field structured this way, others may not contain this structure (i.e. no Additional comments:. The bold letters mark the structure of the subset.

Upvotes: 0

Views: 68

Answers (2)

Jericho
Jericho

Reputation: 213

Give this a try:

WITH cte
AS
    (SELECT
        CASE
            WHEN CHARINDEX('Additional Comment',[field]) = 0 THEN [field]
            WHEN CHARINDEX('Additional Comment',[field]) > 0 THEN 
                LEFT([field],CHARINDEX('Additional Comment',[field])-1)
        END AS Adjusted_field
    FROM table)

SELECT Adjusted_field
FROM cte
WHERE
    Adjusted_field LIKE '%zzz%'

This uses a CTE to basically remove anything after the "Additional Comment" line in the text field. The CASE statement uses CHARINDEX to search for the "Additional Comment" if that string is not found (CHARINDEX =0) then it returns the whole text field, if the string is found then it only returns the text that came before (LEFT) of the "additional comment" string.

Then in the final SELECT query you can search for the desired string in the adjusted text field which now entirely ignores anything that came after the "additional comment" string.

Upvotes: 0

TayTay
TayTay

Reputation: 7160

If there will always be "Additional comments:", you could do something like... 

WHERE field LIKE '%ZZZ%' AND field NOT LIKE 'Additional comments:%ZZZ%'

Upvotes: 1

Related Questions