Regular expression Oracle

Question

I am learning to use regular expressions and I'm using them to limit the results of a search query by using the REGEXP_LIKE in Oracle 11. Placing an example of the data available, I have the following:

Plan navegación 200 MB
Plan navegación 1 GB
Plan navegación 1 GB
Plan de navegacion 3G
Plan de navegacion 4G
Plan de navegacion 3G Empresarial
Plan de navegacion 4G Empresarial
Plan de servicios 3G
Plan de servicios 4G
Plan navegación Datos

I want this result is limited to the following (Only 3G, 4G):

Plan de navegacion 3G
Plan de navegacion 4G
Plan de navegacion 3G Empresarial
Plan de navegacion 4G Empresarial

I am using the following search pattern but I did not properly filtered results:

• Upper(PLAN_GSM),'(NAVEGA){1}|(3G|4G|5G)'
• Upper(PLAN_GSM),'((NAVEGA)+)(3G|4G)+'

I have done several tests and do not find the solution. Someone could give me hints?

Answer 1

You could simply use LIKE, as below:

select *
from mytable
where PLAN_GSM LIKE 'Plan de navegacion _G%';

or use REGEXP_LIKE, as below:

select *
from mytable
where REGEXP_LIKE(PLAN_GSM, '^Plan de navegacion (3|4|5)G(*)');

SQL Fiddle demo

Reference:

Oracle/PLSQL: REGEXP_LIKE Condition on Tech on the Net

Answer 2

You can use this:

SELECT * FROM mytable 
WHERE REGEXP_LIKE(mycolumn, '\APlan de navegacion \dG.*\z', 'c');

• \d represents a digit
• \A is the beginning of the string
• .* greedily matches any characters
• \z is the end of the string