Question 114518 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – is it possible for a query to return the wildcard that matches a given string?

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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, `*`, `%`)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search