I have an old Access query which I'm trying to convert to Oracle SQL. Part of it looks at a string which can have a whole bunch of text and another part of the string is a series of five characters, e.g.:
NNNNN
What I'm trying to do is find where any of those characters are Y but only when in the specific format of being within 5 characters. For example, the whole string might be:
The quick brown fox jumps over the lazy dog NNNNN
I don't want to return this because the five NNNNN does not contain Y.
The current query does something like this:
SELECT *
FROM foo
WHERE
(
bar LIKE '%Y____%' OR
bar LIKE '%_Y___%' OR
bar LIKE '%__Y__%' OR
bar LIKE '%___Y_%' OR
bar LIKE '%____Y%'
)
However, I think this could be better achieved with a single REGEXP_LIKE statement. How could I do this?
Try this WHERE CLAUSE:
where regexp_like(regexp_substr(bar,'[YN]{5}'),'Y')
The example below shows that it returns only those records from the table "foo" where the string of (Ys or Ns) contains a "Y".
select * from foo;
BAR
--------------------------------------------------
The quick brown fox jumps over the lazy dog YNNNN
The quick brown fox jumps over the lazy dog NYNNN
The quick brown fox jumps over the lazy dog NNYNN
The quick brown fox jumps over the lazy dog NNNYN
The quick brown fox jumps over the lazy dog NNNNY
The quick brown fox jumps over the lazy dog NNNNN
The quick brown fox jumps over the lazy dog NNNNN
The quick brown fox jumps over the lazy dog NNNNN
The quick brown fox jumps over the lazy dog NNNNN
The quick brown fox jumps over the lazy dog NNNNN
10 rows selected.
select * from foo where regexp_like(regexp_substr(bar,'[YN]{5}'),'Y');
BAR
--------------------------------------------------
The quick brown fox jumps over the lazy dog YNNNN
The quick brown fox jumps over the lazy dog NYNNN
The quick brown fox jumps over the lazy dog NNYNN
The quick brown fox jumps over the lazy dog NNNYN
The quick brown fox jumps over the lazy dog NNNNY
5 rows selected.