Using REGEXP_LIKE in Oracle

Question

In the MyTable table, I have the following data in the MyField column whose size is 80 characters:

MyField
-------
WA
W
W51534
W
W

I am trying to exclude lines starting with WA through regexp_like. But the following query returns the W51534 line and not the W lines :

select MyField
    from MyTable
    where regexp_like (upper (ltrim (MyField)), '^ [W][^A]');

I would like it to also return lines W. How can I do?

thank you in advance

Edit your question and show us the real problem. – Tim Biegeleisen Nov 30 '18 at 10:25 — Tim Biegeleisen, Nov 30 '18 at 10:25

score 0 · Answer 1 · answered Nov 30 '18 at 09:17

0

You may not even need to use REGEXP_LIKE here, regular LIKE may suffice:

SELECT MyField
FROM MyTable
WHERE MyField NOT LIKE 'WA%';

Demo

answered Nov 30 '18 at 09:17

Tim Biegeleisen

502,043
27
286
360

Yes I know. But it is important for me to do it in regex_like because there are different cases similar to different places. But I don't know why ^[W][[:space:]] don't also works.Thinks – Tony92 Nov 30 '18 at 09:51
the problem is the spaces. When I use rpad, it works. it's not convenient, but it works. regexp_like (upper(rpad(MyField,80,'#')), '^[W][^A]') – Tony92 Nov 30 '18 at 10:15

score 0 · Answer 2 · answered Nov 30 '18 at 12:48

0

Finally, I solved my problem, as I said in comment, by adding the rpad command :
regexp_like (upper(rpad(MyField,80,'#')), '^[W][^A]');
If someone has a better idea, I'm interested.
thinks

answered Nov 30 '18 at 12:48

Tony92

91
7

score 0 · Answer 3 · answered Nov 30 '18 at 14:47

You can negate the regexp_like, so have it match the patterns you don't want:

with mytable(id, myfield) as (
  select 1, 'WA' from dual union all
  select 2, 'W' from dual union all
  select 3, 'W51534' from dual union all
  select 4, 'Z' from dual union all
  select 5, '' from dual
)
select id, myfield
from mytable
where not regexp_like(upper(myfield), '^WA') or 
myfield is null
order by id;

Using REGEXP_LIKE in Oracle

3 Answers3

Demo