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

 
Plan navegación 200 MBPlan navegación 1 GBPlan navegación 1 GBPlan de navegacion 3GPlan de navegacion 4GPlan de navegacion 3G EmpresarialPlan de navegacion 4G EmpresarialPlan de servicios 3GPlan de servicios 4GPlan 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

 
Plan de navegacion 3GPlan de navegacion 4GPlan de navegacion 3G EmpresarialPlan 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%';

 
select *from mytablewhere 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(*)');

 
select *from mytablewhere 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