我有一個 SQL 函式，它回傳一串逗號分隔的國家代碼。
我在另一個表中配置了一些特定代碼，以后可能會洗掉或添加更多代碼。
我想檢查逗號分隔的字串是否只是這些特定國家代碼的組合。也就是說，如果該字串甚至有一個國家代碼而不是指定的國家代碼，它應該回傳 true。
假設我在靜態資料表 GB 和 CH 中配置了兩行。然后我需要以下結果：
| 來自函式的字串 | 結果 |
|---|---|
| 國標 | 錯誤的 |
| 甲烷 | 錯誤的 |
| 國標、國標 | 錯誤的 |
| 國標 | 錯誤的 |
| 國標、法 | 真的 |
| 法文、西班牙文 | 真的 |
| ES,CH | 真的 |
| 中、英、西 | 真的 |
我在 Oracle 19c 上，只能使用此版本可用的功能。另外，我希望對其進行優化。就像我可以檢查字串中值的數量，然后計算每個特定代碼。如果不匹配，那么顯然存在一些其他代碼。但我不想使用回圈。
有人可以建議我一個更好的選擇。
uj5u.com熱心網友回復：
假設靜態表中的所有國家/地區代碼以及逗號分隔字串中的所有標記始終都是兩個字母的字串，您可以執行以下操作：
with
static_data(country_code) as (
select 'GB' from dual union all
select 'CH' from dual
)
, sample_inputs(string_from_function) as (
select 'GB' from dual union all
select 'CH' from dual union all
select 'GB,CH' from dual union all
select 'CH,GB' from dual union all
select 'GB,FR' from dual union all
select 'FR,ES' from dual union all
select 'ES,CH' from dual union all
select 'CH,GB,ES' from dual
)
select string_from_function,
case when regexp_replace(string_from_function,
',| |' || (select listagg(country_code, '|')
within group (order by null)
from static_data))
is null then 'false' else 'true' end as result
from sample_inputs
;
輸出：
STRING_FROM_FUNCTION RESULT
---------------------- --------
GB false
CH false
GB,CH false
CH,GB false
GB,FR true
FR,ES true
ES,CH true
CH,GB,ES true
正則運算式將靜態資料表中的逗號、空格和每個兩個字母的國家/地區代碼替換為null. 如果整個事情的結果是null，那么 csv 中的所有編碼都在靜態表中；這就是你需要測驗的。
這些假設保證了像 GBCH 這樣的代幣（對于像“Great Barrier Country Heat”這樣的國家）不會被錯誤地認為是好的，因為 GB 和 CH 是分開的。
uj5u.com熱心網友回復：
您可以將 csv 列轉換為表并使用 EXISTS。例如
with tbl(id,str) as
(
SELECT 1,'GB,CH' FROM DUAL UNION ALL
SELECT 2,'GB,CH,FR' FROM DUAL UNION ALL
SELECT 3,'GB' FROM DUAL
),
countries (code) as
(SELECT 'GB' FROM DUAL UNION ALL
SELECT 'CH' FROM DUAL
)
select t.* ,
case when exists (
select 1
from xmltable(('"' || REPLACE(str, ',', '","') || '"')) s
where trim(s.column_value) not in (select code from countries)
)
then 'true' else 'false' end flag
from tbl t
uj5u.com熱心網友回復：
一種選擇是逐個匹配國家代碼，然后根據提供的文字作為引數確定是否存在額外的不匹配國家。
考慮到上面的邏輯，下面的 FULL JOIN 會有所幫助
WITH
FUNCTION with_function(i_countries VARCHAR2) RETURN VARCHAR2 IS
o_val VARCHAR2(10);
BEGIN
SELECT CASE WHEN SUM(NVL2(t.country_code,0,1))=0 THEN 'false'
ELSE 'true'
END
INTO o_val
FROM (SELECT DISTINCT REGEXP_SUBSTR(i_countries,'[^ ,] ',1,level) AS country
FROM dual
CONNECT BY level <= REGEXP_COUNT(i_countries,',') 1) tt
FULL JOIN t
ON tt.country = t.country_code;
RETURN o_val;
END;
SELECT with_function(<comma-seperated-parameter-list>) AS result
FROM dual
Demo
uj5u.com熱心網友回復：
這是一種解決方案
with cte as (select distinct s,regexp_substr(s, '[^,] ',1, level) code from strings connect by regexp_substr(s, '[^,] ', 1, level) is not null ) select s string,min(case when exists (select * from countries where cod = code) then 'yes' else 'no'end) all_found from cte group by s order by s;
字串 | 全部找到 :----- | :-------- 中文 | 是的 中國，英國 | 是的 西式 | 不 ES,CH | 不 法語 | 不 國標 | 是的 國標、中標| 是的 國標、西歐 | 不
db<>在這里擺弄
uj5u.com熱心網友回復：
如果靜態表中有少量值，那么最簡單的方法可能不是從函式中拆分值，而是使用以下方法從靜態表中生成所有值組合：
SELECT SUBSTR(SYS_CONNECT_BY_PATH(value, ','), 2) AS combination
FROM static_table
CONNECT BY NOCYCLE PRIOR value != value;
其中，對于樣本資料：
CREATE TABLE static_table(value) AS
SELECT 'GB' FROM DUAL UNION ALL
SELECT 'CH' FROM DUAL;
輸出：
組合 國標 國標、國標 甲烷 國標
然后您可以使用一個簡單的CASE運算式將您的字串輸出到組合中：
SELECT function_value,
CASE
WHEN function_value IN (SELECT SUBSTR(SYS_CONNECT_BY_PATH(value, ','), 2)
FROM static_table
CONNECT BY NOCYCLE PRIOR value != value)
THEN 'false'
ELSE 'true'
END AS not_matched
FROM string_from_function;
其中，對于樣本資料：
CREATE TABLE string_from_function(function_value) AS
SELECT 'GB' FROM DUAL UNION ALL
SELECT 'CH' FROM DUAL UNION ALL
SELECT 'GB,CH' FROM DUAL UNION ALL
SELECT 'CH,GB' FROM DUAL UNION ALL
SELECT 'GB,FR' FROM DUAL UNION ALL
SELECT 'FR,ES' FROM DUAL UNION ALL
SELECT 'ES,CH' FROM DUAL UNION ALL
SELECT 'CH,GB,ES' FROM DUAL;
輸出：
FUNCTION_VALUE 未匹配 國標 錯誤的 甲烷 錯誤的 國標、國標 錯誤的 國標 錯誤的 國標、法 真的 法文、西班牙文 真的 ES,CH 真的 中、英、西 真的
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/472559.html