How to perform a wildcard search in PostgreSQL

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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)

Answer 4

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

Answer 5

Have You tried Like operator along with % . In your case it would be like

SELECT * FROM customers WhERE name LIKE 'Aa%'  

Answer 6

LIKE operator alongwith wildcard caharacters can be used in the wildcard search. Following queries would be helpful for you: Query that search people starting name with 'Aa' and ending with any character.

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

and if u want to get customers whose name end with any character, wildcard character can be used at the end for the search:

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

hope it will help!!!

Answer 7

You can use two approaches generally for wildcard entries depending on whether case sensitivity is important or not.

SELECT * FROM table_name WHERE name LIKE 'initial_chars%';

In your case table name is customers and the wildcard is Aa%. % allows for anything after the initial_chars.

If case sensitivity is important you can use ILIKE instead of LIKE.

Answer 8

you can use the LIKE operator along with the % wildcard symbol.

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

This query will retrieve all rows from the customers table where the name column starts with "Aa" and is followed by any characters.

Answer 9

Checking for if the query literally startswith 'Aa'? then you query should work fine with select ... from ... where name LIKE 'Aa%'.But if 'Aa' means just to match a case insensitive 'a' then the following query should work select ... from ... where name ILIKE 'A%'.

Answer 10

In order to perform wildcard search in PostgreSQL, LIKE operator along with wildcard symbol % can be used. In your case, the query will be like this:

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

Answer 11

An easy and correct way to do wildcard search in postgresql is to use the following query.

    SELECT * 
    FROM Customers
    WHERE name like 'Aa%';

This query will return all the details of the customers, whose name starts with "Aa".

Hope this helps.