Determine if a column has two equal vowels-CodePudding

How to determine if a column has two equal vowels in SQL Server?

For example 'maria' has two 'a' characters.

select
    *
from
    hr.locations
where
    state_province is null
    and
    city like '...'  <-- ?

CodePudding user response：

You want to look for strings with a vowel appearing multiple times. You already have city like '...'.

Now, you may have in mind somehing like city like '%[aeiou]%<the same vowel>%', and you wonder how to make this <the same vowel> work. It simply is not possible; such reference is not available in LIKE. Instead find the expression for a single vowel: city like '%a%a%'. Then use OR for the different vowels:

select *
from hr.locations
where state_province is null
and
(
  city like '%a%a%' or
  city like '%e%e%' or
  city like '%i%i%' or
  city like '%o%o%' or
  city like '%u%u%'
);

If your city column is case sensitive, and you want to find 'Anna' in spite of one A being in upper case and the other in lower case, make this lower(city) like '%a%a%'.

CodePudding user response：

If your intention is to find those entries that contain exactly two equal vowels:

One way to find out how often a certain character (in your case a vowel) appears in a string is to first take the length of the entire string.

As second step, replace your character by an empty string and build the length of the new string. This will be the length without all occurences of this character.

If the entire length reduced by the new length is 2, this will mean the character occurs exactly two times in your string. So, you can create a query repeating this idea for every vowel, something like this:

SELECT yourcolumn
FROM yourtable
WHERE
LEN (yourcolumn) - LEN(REPLACE(yourcolumn,'a','')) = 2
OR LEN (yourcolumn) - LEN(REPLACE(yourcolumn,'e','')) = 2
OR LEN (yourcolumn) - LEN(REPLACE(yourcolumn,'i','')) = 2
OR LEN (yourcolumn) - LEN(REPLACE(yourcolumn,'o','')) = 2
OR LEN (yourcolumn) - LEN(REPLACE(yourcolumn,'u','')) = 2;

If your intention is to find those entries that contain at least two equal vowels: Just replace the "=" by ">=" or use LIKE instead.

Try out here: db<>fiddle