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

Can PostgreSQL do wildcard searches?

I am working with a PostgreSQL database and I need to perform a wildcard search in a specific column. How can I write a query to achieve this?

I have a table called customers with a column name that stores customers names. I want to search for customers whose names start with "Aa" and end with any characters.

Tags:

4

Answers


  1. 0

    Something like:

    select *
from users u
where u.name like 'Aa%'

    Or if you want case insensitive:

    select *
from users u
where u.name ilike 'Aa%'

    Postgres also supports regular expressions, but that might be overkill compared to what you want to do. See https://www.postgresql.org/docs/current/functions-matching.html for more information.

    Login or Signup to reply.
  2. 0

    In PostgreSQL, you can use the following command:

    SELECT *
FROM customers
WHERE name LIKE 'Aa%';

    If you want to perform a case-insensitive search, you can use the ILIKE operator instead

    For Apache AGE, you can use this instead:

    SELECT * FROM cypher('graph_name', $$
    MATCH (v:customers)
    WHERE v.name STARTS WITH "Aa"
    RETURN v.name
$$) AS (name agtype);

    You can read more here about Apache AGE operators.

    Login or Signup to reply.
  3. 0

    In addition to other answers, according to the documentation, you can make a case-insensitive search adding (?i) at the beginning of the string. For example, if you have a database with the following information:

    SELECT * FROM cypher('graph_name', $$
CREATE (:Example {name: 'aaaa'}),
       (:Example {name: 'AAAA'}),
       (:Example {name: 'aAaA'}),
       (:Example {name: 'AaAa'})
$$) AS (result agtype);

    You can perform the following case-insensitive search and return the following results:

    SELECT * FROM cypher('graph_name', $$
        MATCH (v:Example)
        WHERE v.name =~ '(?i)Aa'
        RETURN v.name
$$) AS (names agtype);

 names  
--------
 "aaaa"
 "AAAA"
 "aAaA"
 "AaAa"
(4 rows)
    Login or Signup to reply.
  4. 0

    You can search for customers names that starts with "Aa" with this script:

    SELECT * FROM customers 
WHERE name LIKE 'Aa%';

    If you are looking the case insensitive, so in that case you can use ILIKE instead of LIKE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search