CODEWITHSUNDEEP
sqloracle-database
user13576801
user13576801

Reputation:

SQL Developer Regexp_Like search for phrase within long string

All,

I am trying to search for a phrase ("Observed TRD3 Ufls" within a long string, and I am having trouble. Sometimes the sub string could be at the beginning, sometimes in the middle, and sometimes at the end. I am trying to something like

    MAX(CASE WHEN REGEXP_LIKE(B.shipment,'Observed TRD3 Ufls','i') THEN 1 ELSE 0 END) OVER (PARTITION BY d.Status) AS Tag

Sometimes observed can be observe so I need to be able to get both, and lastly it needs to be case insensitive.

Every result I review is always 0 instead of 1. Any help would be appreciated!

Upvotes: 0

Views: 396

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

It seems to me that your current regex logic should appear in a where clause:

SELECT *
FROM yourTable
WHERE REGEXP_LIKE(shipment, 'Observed TRD3 Ufls', 'i');

Your current call to REGEXP_LIKE should already be doing a case insensitive search for the text. Note that strictly speaking, if you want to find the exact text Observed TRD3 Ufls inside the text, you should be using word boundaries:

SELECT *
FROM yourTable
WHERE REGEXP_LIKE(shipment, '(^|\s)Observed TRD3 Ufls(\s|$)', 'i');

This will match the text at the start, middle, or end.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

Instead of REGEXP, You can simply use the like as follows:

upper(B.shipment) LIKE '%OBSERVED TRD3 UFLS%'

Please note that It is working fine for me even if the matching string is at the start, middle, or end of the column

Upvotes: 1

Related Questions