Query table with different list of variables using like

Question

I call a webservice and i get a list of codes: A, B, C, D, E, F etc.

In my problem i get A, B, C.

There's a table where you can create sets with the above codes and for each set you can add a specific message so the end user can understand the codes. For example A means "ok", B means "you can log in" So for the set A, B a message could be "you can log in". The code sets are saved in one column (codes).

In my problem i query the table by using the following query:

Select setid, codes, messagedescr from table1 where setid = (select max(setid) from  table1 
                        And codes Like '%A%'  
                        And codes Like '%B%'  
                        And codes Like '%C%');

This query finds a row but it's wrong, the column "codes" contains the following codes: A, B, C, D.

For example:

setid   codes       messagedescr
1       A, B, C, D  You can login
2       B, C, D     You can login for one day
3       A, C, E     You can login but update your profile
4       B, C, E, F  You cannot login

I don't know the order of the codes from the webservice and i don't how the codes are saved in the table, so i had to make something without order, this why i used the Likes. There are 25 codes: A, B, C, etc

How can i fix the query so i can find the correct message?

Thanks!

Update: Thank you all for your answers and specially the more detailed answers that had extra work such as creating the tables. The table might have from 10-20 rows. One case is to create another table for the codes and join it with the set ids. Or the other case is to count the lengths of the codes from the service and see if they match with the length in the table.

Can you please post some sample data and desired result? ( formatted text, please) — Aleksej, Feb 15 '17 at 10:57
@Tsiftelis Thanasis,If you post A,B,C means whict message you want dispaly.ie A's message or B's message or C's message what is your priority. — Mansoor, Feb 15 '17 at 10:59
This is a consequence of poor DB design catching up with you. Don't store multiple codes in a single column; use a separate table. — Sergey Kalinichenko, Feb 15 '17 at 11:00
@Tsiftelis Thanasis, how many codes do you get back from the web service? Always the same number? — Serge, Feb 15 '17 at 11:02
I updated with a sample of the data. But as i think it i agree with @dasblinkenlight. I will create another table for the codes. — Tsiftelis Thanasis, Feb 15 '17 at 11:06
Add `AND LEN(codes) = @Number_Of_Codes_Received` and your current query will work. — Serge, Feb 15 '17 at 11:07
When developing something that needs to read the database you should know how that portion of the data is configured - what the rules are. Without that it is just luck/guesswork as to whether you are getting what you want. Even if the rules are inconsistent/poorly modelled/illogical you still need to know how it is set up. — BriteSponge, Feb 15 '17 at 11:09
The service can return from 1-X number of codes with no order or priority and max 25. Also the table doesn't have priority or order. I think the solution with the length is the easiest and faster and since the table won't be large it won't affect something else. — Tsiftelis Thanasis, Feb 15 '17 at 11:56

Aleksej · Answer 1 · 2017-02-15T11:46:10.400

If I understand well your need, this could be a way.

Say you have a table like this:

create table yourTable(setid, codes, messagedescr) as ( 
  select 1,       'A, B, C, D',  'You can login' from dual union all
  select 2,       'B, C, D'   ,  'You can login for one day' from dual union all
  select 3,       'A, C, E'   ,  'You can login but update your profile' from dual union all
  select 4,       'B, C, E, F',  'You cannot login' from dual
).

This could be a way:

with inputData(codes) as (
    select listagg(trim (regexp_substr(input_codes, '[^,]+', 1, level))) within group ( order by trim (regexp_substr(input_codes, '[^,]+', 1, level)))
    from ( select 'A, D, C, B' as input_codes from dual )  /* the input string */
    CONNECT BY instr(input_codes, ',', 1, level - 1) > 0
)    
select *
from inputData 
    inner join (
                select listagg(trim (regexp_substr(codes, '[^,]+', 1, level)))
                         within group ( order by trim (regexp_substr(codes, '[^,]+', 1, level))) as codes,
                        messagedescr
                from yourTable  
                CONNECT BY instr(codes, ',', 1, level - 1) > 0
                  and prior setId = setId
                  and prior sys_guid() is not null
                group by setId, messagedescr
               )
      using (codes)

The idea here is to split your input string in many rows, and then aggregate the resulting rows in alphabetic order, then apply the same order on the values in table and then check that the ordered strings are equal.

This part is used to split, order and aggregate the input values, so that the result is an ordered string:

select listagg(trim (regexp_substr(input_codes, '[^,]+', 1, level))) within group ( order by trim (regexp_substr(input_codes, '[^,]+', 1, level)))
    from ( select 'A, D, C, B' as input_codes from dual )  /* the input string */
    CONNECT BY instr(input_codes, ',', 1, level - 1) > 0

gives:

ABCD

This part is used to do the same on your table:

select listagg(trim (regexp_substr(codes, '[^,]+', 1, level)))
         within group ( order by trim (regexp_substr(codes, '[^,]+', 1, level))) as codes,
        messagedescr
from yourTable  
CONNECT BY instr(codes, ',', 1, level - 1) > 0
  and prior setId = setId
  and prior sys_guid() is not null
group by setId, messagedescr

gives:

CODES      MESSAGEDESCR
---------- -------------------------------------
ABCD       You can login
BCD        You can login for one day
ACE        You can login but update your profile
BCEF       You cannot login

The join between these partial results is quite straightforward and simply checks if a value (ordered) exists in your table that corresponds to the (ordered) input string.

MT0 · Answer 2 · 2017-09-15T12:50:09.897

Oracle Setup:

Start with a simple function to split a delimited string into a collection:

CREATE OR REPLACE TYPE stringlist AS TABLE OF VARCHAR2(20)
/

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
  p_result       stringlist := stringlist();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

and some sample data:

CREATE TABLE your_table( setid, codes, messagedescr ) 
  SELECT 1, 'A,B,C,D', 'You can login' FROM DUAL UNION ALL
  SELECT 2, 'B,C,D',   'You can login for one day' FROM DUAL UNION ALL
  SELECT 3, 'A,C,E',   'You can login but update your profile' FROM DUAL UNION ALL
  SELECT 4, 'B,C,E,F', 'You cannot login' FROM DUAL;

Then you can do (passing your input C,A,B - in any order - as the bind parameter :your_code):

SELECT *
FROM   (
  SELECT *
  FROM   your_table
  WHERE  split_string( codes ) SUBMULTISET OF split_String( :your_code )
  ORDER BY setid DESC
)
WHERE ROWNUM = 1;

and it will output the row with the highest matching set of codes.

Note: The above example assumes that you need to match all the codes in the table to codes in your input string. If you only need to match at least one then you can use:

WHERE split_string( codes ) MULTISET INTERSECT split_String( :your_code ) IS NOT EMPTY

Query table with different list of variables using like

2 Answers2

Linked