translate query from Oracle to Postgres-CodePudding

I need to translate this query from Oracle to Postgres:

select
case when (not regexp_like(upper(location),'^[0-9]{1,5} [A-Z]') or not regexp_like(upper(location),'^[0-9]{1,5}.* [a-z]') or regexp_like(upper(location),'^[0-9]{1,5}')) and regexp_like(upper(modelname),'CEDMI') then '74'
when (not regexp_like(upper(location),'^[0-9]{1,5} [A-Z]') or not regexp_like(upper(location),'^[0-9]{1,5}.* [a-z]') or regexp_like(upper(location),'^[0-9]{1,5}')) and regexp_like(upper(modelname),'CEDNA') then '768'
when (not regexp_like(upper(location),'^[0-9]{1,5} [A-Z]') or not regexp_like(upper(location),'^[0-9]{1,5}.* [a-z]') or regexp_like(upper(location),'^[0-9]{1,5}')) and regexp_like(upper(modelname),'CEDRM') then '598'
when (not regexp_like(upper(modelname),'CEDMI') or not regexp_like(upper(modelname),'CEDNA') or not regexp_like(upper(modelname),'CEDRM')) and (regexp_like(upper(location),'^[A-Z]') or location is null) then null
else location
end as LOCATION
from stagingarea.ENEL_TLC_T_DEVICE

can someone help me?

thanks in advance for your attention and support

CodePudding user response：

In postgres the operateur ~ performs a regex comparison as thus replaces the Oracle function regexp_like(). Your query therefore becomes.
I would like to alert your attention that the test not upper(location) ~ '^[0-9]{1,5}.* [a-z]' will always be true because the application of upper() makes it impossible to match [a-z].

create table ENEL_TLC_T_DEVICE(
location varchar(100),
modelname varchar(100)   );

✓

insert into ENEL_TLC_T_DEVICE (location, modelname) values
('12A','CEDMI'),
('12A','CEDRM'),
('12A','CEDNA'),
('12AA','CEDMI'),
('12AA','CEDRM'),
('12AA','CEDNA'),
(null,null);

7 rows affected

select
location, 
modelname,
case when (not  upper(location) ~ '^[0-9]{1,5} [A-Z]' or not  upper(location) ~ '^[0-9]{1,5}.* [a-z]' or  upper(location) ~ '^[0-9]{1,5}') and  upper(modelname) ~ 'CEDMI' then '74'
when (not  upper(location) ~ '^[0-9]{1,5} [A-Z]' or not  upper(location) ~ '^[0-9]{1,5}.* [a-z]' or  upper(location) ~ '^[0-9]{1,5}') and  upper(modelname) ~ 'CEDNA' then '768'
when (not  upper(location) ~ '^[0-9]{1,5} [A-Z]' or not  upper(location) ~ '^[0-9]{1,5}.* [a-z]' or  upper(location) ~ '^[0-9]{1,5}') and  upper(modelname) ~ 'CEDRM' then '598'
when (not  upper(modelname) ~ 'CEDMI' or not  upper(modelname) ~ 'CEDNA' or not  upper(modelname) ~ 'CEDRM') and  ( upper(location) ~ '^[A-Z]' or location is null) then null
else location
end as LOCATION
from ENEL_TLC_T_DEVICE;

location | modelname | location
:------- | :-------- | :-------
12A      | CEDMI     | 74      
12A      | CEDRM     | 598     
12A      | CEDNA     | 768     
12AA     | CEDMI     | 74      
12AA     | CEDRM     | 598     
12AA     | CEDNA     | 768     
null     | null      | null

db<>fiddle here