sqlpostgresqlwhere-clausesql-likein-clause

Select rows from table with specific string value endings from list


I have a table with two columns item_name, value where item_names looks like "abracadabra_prefix.tag_name". And I need to select rows with tag_names from a list that doesn't have a prefix.

Should be somthing like:

tag_names = ['f1', 'k500', '23_g']

SELECT * FROM table WHERE item_name IN (LIKE "%{tag_names});

input table:

item_name value
fasdaf.f1 1
asdfe.f2 2
eywvs.24_g 2
asdfe.l500 2
asdfe.k500 2
eywvs.23_g 2

output table:

item_name value
fasdaf.f1 1
asdfe.k500 2
eywvs.23_g 2

I have tried concatenating a string in a loop to get a query like this:

SELECT * FROM table WHERE item_name LIKE '%f1' OR item_name LIKE '%k500' OR item_name LIKE '%23_g';

But I can have from 1 to 200 tags, and with a large number of tags, this makes the query too complicated,as I understand it.


Solution

  • You can extract the suffix of item_name using substring with regexp and then use the any operator for comparison in the where clause.

    select * from the_table
    where substring (item_name from '\.(\w+)$') = any('{f1,k500,23_g}'::text[]);
    

    SQL fiddle demo
    If you intend to use the query as a parameterized one then it will be convenient to replace '{f1,k500,23_g}'::text[] with string_to_array('f1,k500,23_g', ','), i.e. pass the list of suffixes as a comma-separated string. Please note that this query will result in a sequential scan.