Regular expression Oracle

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

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

Advertisement

Answer