我需要將此查詢從 Oracle 轉換為 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
有人能幫我嗎？
提前感謝您的關注和支持
uj5u.com熱心網友回復：
在 postgres 中，operator~執行正則運算式比較，從而替換了 Oracle 函式regexp_like()。您的查詢因此變為。
我想提醒您注意，該測驗not upper(location) ~ '^[0-9]{1,5}.* [a-z]'將始終為真，因為應用程式upper()無法匹配[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 行受影響
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;
位置 | 型號名 | 地點 :------- | :-------- | :-------- 12A | CEDMI | 74 12A | CEDRM | 598 12A | CEDNA | 768 12AA | CEDMI | 74 12AA | CEDRM | 598 12AA | CEDNA | 768 空 | 空 | 空值
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/454338.html
標籤：sql 正则表达式 PostgreSQL 甲骨文
上一篇：SQL通過有兩個專案選擇不同的組