Query rows where first_name contains at least 2 vowels, and the number of occurences of each vowel i-CodePudding

I have the following problem: Show all rows in table where column first_name contains at least 2 vowels (a, e, i, o, u), and the number of occurences of each vowel is the same.

Valid example: Alexander, "e" appears 2 times, "a" appears 2 times. That is coreect.

Invalid example: Jonathan, it has 2 vowels (a, o), but "o" appears once, and "a" appears twice, the number of occurences is not equal.

I've solved this problem by calculating each vowel, and then verify every case (A E, A I, A O etc. Shortly, each combination of 2, 3, 4, 5). With that solution, I have a very long WHERE. Is there any shorter way and more elegant and simple?

CodePudding user response：

This is how I solved it in TSQL in MS SQL Server 2019. I know its not exactly what you wanted. Just an interesting thing to try. Thanks for that.

DROP TABLE IF EXISTS #Samples

SELECT n.Name
INTO   #Samples
FROM
       (
           SELECT 'Ravi' AS Name
           UNION
           SELECT 'Tim'
           UNION
           SELECT 'Timothe'
           UNION
           SELECT 'Ian'
           UNION
           SELECT 'Lijoo'
           UNION
           SELECT 'John'
           UNION
           SELECT 'Jami'
       ) AS n

SELECT   g.Name,
         IIF(MAX (g.Repeat) = MIN (g.Repeat) AND SUM (g.Appearance) >= 2, 'Valid', 'Invalid') AS Validity
FROM
         (
             SELECT      v.value,
                         s.Name,
                         SUM (LEN (s.Name) - LEN (REPLACE (s.Name, v.value, ''))) AS Repeat,
                         SUM (IIF(s.Name LIKE '%'   v.value   '%', 1, 0)) AS Appearance
             FROM        STRING_SPLIT('a,e,i,o,u', ',') AS v
             CROSS APPLY #Samples AS s
             GROUP BY    v.value,
                         s.Name 
         ) AS g
WHERE    g.Repeat > 0
GROUP BY g.Name

Output

we can replace STRING_SPLIT with a temp table for supporting lower versions

DROP TABLE IF EXISTS #Vowels

SELECT C.Vowel
INTO   #Vowels
FROM
       (
           SELECT 'a' AS Vowel
           UNION
           SELECT 'e'
           UNION
           SELECT 'i'
           UNION
           SELECT 'o'
           UNION
           SELECT 'u'
       ) AS C

SELECT   g.Name,
         IIF(MAX (g.Repeat) = MIN (g.Repeat) AND SUM (g.Appearance) >= 2, 'Valid', 'Invalid') AS Validity
FROM
         (
             SELECT      v.Vowel,
                         s.Name,
                         SUM (LEN (s.Name) - LEN (REPLACE (s.Name, v.Vowel, ''))) AS Repeat,
                         SUM (IIF(s.Name LIKE '%'   v.Vowel   '%', 1, 0)) AS Appearance
             FROM        #Vowels AS v
             CROSS APPLY #Samples AS s
             GROUP BY    v.Vowel,
                         s.Name
         ) AS g
WHERE    g.Repeat > 0
GROUP BY g.Name

CodePudding user response：

From Oracle 12, you can use:

SELECT name
FROM   table_name
       CROSS JOIN LATERAL(
         SELECT 1
         FROM   (
           -- Step 2: Count the frequency of each vowel
           SELECT letter,
                  COUNT(*) As frequency
           FROM   (
             -- Step 1: Find all the vowels
             SELECT REGEXP_SUBSTR(LOWER(name), '[aeiou]', 1, LEVEL) AS letter
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT(LOWER(name), '[aeiou]')
           )
           GROUP BY letter
         )
         -- Step 3: Filter out names where the number of vowels are
         --   not equal or the vowels do not occur at least twice
         --   and there are not at least 2 different vowels.
         HAVING MIN(frequency) >= 2
         AND    MIN(frequency) = MAX(frequency)
         AND    COUNT(*)       >= 2
       );

Which, for the sample data:

CREATE TABLE table_name (name) AS
SELECT 'Alexander' FROM DUAL UNION ALL
SELECT 'Johnaton' FROM DUAL UNION ALL
SELECT 'Anna' FROM DUAL;

Outputs:

NAME

Alexander

NAME
Alexander

db<>fiddle here