is it possible for a query to return the wildcard that matches a given string?-CodePudding

I have the following table in postgres:

Table "public.items"
    
   Column    |           Type           | Collation | Nullable |                Default                
------------- -------------------------- ----------- ---------- ---------------------------------------
 id          | integer                  |           | not null | nextval('items_id_seq'::regclass)
 wildcard    | character varying(255)   |           | not null |

The wildcard column value contains wildcards of the form:stackoverflow*.

This should match any word that begins with 'stackoverflow'.

How can I locate the record that contains the a matching wild card?

For example, given 'stackoverflow.com' I would like to return all wildcards matching it. something like

CodePudding user response：

There is no built-in reverse match operator, but you can just swap the position of the arguments to get the reversed match:

select * from items where 'stackoverflow.com' LIKE items.wildcard;

Now, you can't get use of an index this way around, but that is only a barrier to performance, it won't stop you from running the query and getting an answer.

CodePudding user response：

Store your wildcards with % instead of * and use like:

select *
from items
where 'stackoverflow.com' like wildcard

Or if you must use *, same but replace * with %:

select *
from items
where 'stackoverflow.com' like replace(wildcard, `*`, `%`)