I have an SQL function that returns me a string of comma separated country codes.
I have configured some specific codes in another table and I may remove or add more later.
I want to check if the comma separated string is only the combination of those specific country codes or not. That said, if that string is having even a single country code other than the specified ones, it should return true.
Suppose I configured two rows in the static data table GB and CH. Then I need below results:
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
I am on Oracle 19c and can use only the functions available for this version. Plus I want it to be optimised. Like I can check the number of values in string and then count for each specific code. If not matching then obviously some other codes are present. But I don't want to use loops.
Can someone please suggest me a better option.
CodePudding user response：
You can convert a csv column to a table and use EXISTS. For example
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
CodePudding user response：
One option is to match the country codes one by one, and then determine whether there exists an extra non-matched country from the provided literal as parameter.
The following one with FULL JOIN would help by considering the logic above
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
CodePudding user response：
Assuming that all country codes in the static table, as well as all tokens in the comma-separated strings, are always exactly two-letter strings, you could do something like this:
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
;
Output:
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
The regular expression replaces comma, space, and every two-letter country code from the static data table with null. If the result of the whole thing is null, then all coded in the csv are in the static table; that's what you need to test for.
The assumptions guarantee that a token like GBCH (for a country like "Great Barrier Country Heat") would not be mistakenly considered OK because GB and CH are OK separately.
CodePudding user response：
Here is one solution
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;
STRING | ALL_FOUND :----- | :-------- CH | yes CH,GB | yes ES | no ES,CH | no FR | no GB | yes GB,CH | yes GB,ES | no
db<>fiddle here
CodePudding user response：
If you have a small number of values in the static table then the simplest method may not be to split the values from the function but to generate all combinations of values from the static table using:
SELECT SUBSTR(SYS_CONNECT_BY_PATH(value, ','), 2) AS combination
FROM static_table
CONNECT BY NOCYCLE PRIOR value != value;
Which, for the sample data:
CREATE TABLE static_table(value) AS
SELECT 'GB' FROM DUAL UNION ALL
SELECT 'CH' FROM DUAL;
Outputs:
COMBINATION GB GB,CH CH CH,GB
Then you can use a simple CASE expression to your string output to the combinations:
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;
Which, for the sample data:
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;
Outputs:
FUNCTION_VALUE NOT_MATCHED GB false CH false GB,CH false CH,GB false GB,FR true FR,ES true ES,CH true CH,GB,ES true
db<>fiddle here