SQL: Select with "Like" 2 elements-CodePudding

How can I select any person that has two letters "a" in the name, on a PostgreSQL database ? I try select client from xclient where client like '%a%' but this return all people that contain "a" in the name. I necessarily need to use "Like".

Should the results include people with two 'a's in their name Example: "Marta". Can't include "Alaia" for example.

CodePudding user response：

Try this:

select client from xclient where client like '%a%a%'

CodePudding user response：

Your question and comments are very vague.

My interpretation is that...

You consider A and a to be the same character

So, you should use ILIKE not LIKE

You want names with two A or a, but not more than two, and it doesn't matter if they're next to each other.

WITH
  xclient AS
(
            SELECT 'Baat'   AS client
  UNION ALL SELECT 'Marta'
  UNION ALL SELECT 'Alaia'
  UNION ALL SELECT 'Angalaia'
  UNION ALL SELECT 'Aana'
  UNION ALL SELECT 'Arcanjamana'
)
SELECT
  *
FROM
  xclient
WHERE
      client     ILIKE '%a%a%'
  AND client NOT ILIKE '%a%a%a%'

Demo : https://dbfiddle.uk/?rdbms=postgres_14&fiddle=a0894fdd211087db552537e40d28d9bf

CodePudding user response：

I am almost sure this code works in the examples you said:

select client from xclient where client like '%a%a%' and client not like '%a%a%a%' and client not like '%a