-1

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.

Hocks
  • 3
  • 6
  • 2
    Should the results include people with three 'a's in their name? –  Nov 24 '21 at 20:01
  • 1
    In Danish, consecutive 'aa' is considered to be one character, 'å' (i.e. not two 'a'.) – jarlh Nov 24 '21 at 20:06
  • @HappyMoose Should the results include people with two 'a's in their name Example: "Marta". Can't include "Alaia" for example. – Hocks Nov 25 '21 at 11:22
  • 2
    Part of the issue here is that you're being very imprecise. What are the ***full and exact*** business rules that you're trying to implement? – MatBailie Nov 25 '21 at 11:26
  • That's simple, limit of "A" in the name must be two and minimal must two. Correct Example "Marta". Incorrect Example: "Angalaia" or "Aana" – Hocks Nov 25 '21 at 11:38

3 Answers3

1

Try this:

select client from xclient where client like '%a%a%'
Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
  • This is the return of all people with two "a"s in a row. Example: "Aana" – Hocks Nov 25 '21 at 11:19
  • @VictorHugo - `LIKE '%a%a%' AND NOT LIKE '%a%a%a%'` will get everyone with two `a`'s in their name, but exclude anyone with 3 or more `a`'s in their name. That will exclude `Aana`, but what do you want to do with `Baat`? – MatBailie Nov 25 '21 at 11:27
  • @MatBailie That's include for example "Aangala". The limit is two "A" and minimal is two "A" – Hocks Nov 25 '21 at 11:33
  • It absolutely does NOT include `Aangala` the `NOT LIKE` explicitly excludes it. – MatBailie Nov 25 '21 at 13:12
0

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%aa%' and client not like '%aa%a%' and client not like '%aa%aa%'

There is no doubt that you could optimize this code using the substring keyword, but, anyway, this should perform as you wished.

joangm_
  • 27
  • 1
  • 12
  • That's include for example "Aangala". The limit is two "A" and minimal is two "A" – Hocks Nov 25 '21 at 11:35
  • Try the new code (I edited my answer). – joangm_ Nov 25 '21 at 11:49
  • Yes, that's is correct, but if the name is "Arcanjamana" ? You understand that i need a limitator ? – Hocks Nov 25 '21 at 11:53
  • I can't edit my code every time someone enters a different name. – Hocks Nov 25 '21 at 11:54
  • Okay, now I got what you mean. In your question, even though you specify two examples you do not want to appear, _Marta_ and _Alaia_, you only state that you **do not want two a's**, that is why we all got confused. Now, understanding the issue, I suggest you checking [REGEXP](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp) to address this problem. You got an example [here](https://stackoverflow.com/questions/18780194/how-to-regex-in-a-mysql-query). I also encourage you to use [this website](https://regex101.com/) to do your tests. Hope it helps! – joangm_ Nov 25 '21 at 12:09
0

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

MatBailie
  • 83,401
  • 18
  • 103
  • 137