Search code examples
sqloracle-databaseoracle19c

Oracle SQL : Check if specified words are present in comma separated string

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.

Solution

  • 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.