CODEWITHSUNDEEP
sqlregexoracle-database
Silver.Rainbow
Silver.Rainbow

Reputation: 435

ORACLE: How to use regexp_like to find a string with single quotes between two characters?

I need to query the DB for all records that have two single quite between characters. Example : We've, who's.

I have the regex https://regex101.com/r/6MtB9j/1 but it doesn't work with REGEXP_LIKE.

Tried this

SELECT content
FROM MyTable
WHERE REGEXP_LIKE (content, '(?<=[a-zA-Z])''(?=[a-zA-Z])')

Appreciate the help!

Upvotes: 1

Views: 1756

Answers (3)

Aleksej
Aleksej

Reputation: 22949

If I understand well, you may need something like

regexp_count(content, '[a-zA-Z]''[a-zA-Z]') = 2.

For example, this

with myTable(content) as
(
    select q'[what's]' from dual union all
    select q'[who's, what's]' from dual union all
    select q'[who's, what's, I'm]' from dual
)        
select *
from myTable
where regexp_count(content, '[a-zA-Z]''[a-zA-Z]') = 2

gives

CONTENT
------------------
who's, what's

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If you just need a single quote in a string, you can use:

where content like '%''%'

If they specifically need to be letters, then you need a regular expression:

regexp_like(content, '[a-zA-Z][''][a-zA-Z]')

or:

regexp_like(content, '[a-zA-Z]\'[a-zA-Z]')

Upvotes: 0

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626926

Oracle regex does not support lookarounds.

You do not actually need lookaround in this case, you can use

SELECT content
FROM MyTable
WHERE REGEXP_LIKE (content, '[a-zA-Z]''[a-zA-Z]')

This will work since REGEXP_LIKE only attempts one match, and if there is a match, it returns true, otherwise, false (eventually, fetching a record or not).

Lookarounds are useful in case you need to replace or extract values, when matches may overlap.

Upvotes: 1

Related Questions