for this assignment I have to return the names of the columns in all tables from a specific user where the column name has at least 2 vowels and the its length is superior to 8. I know that sql can handle regular expression unfortunately I don't know if it can be used in this case.

Here is a practical example of what I want my request to do.

Here is the existing tables and columns

Table name	Column name
Person	identification
Person	name
Person	last_name
City	continent
City	country
City	name
City	Strength

Here is the desired output from the request

Table name	Column name
Person	identification
Person	last_name
City	continent

I'm using Oracle DMBS. Thank you for your time

CodePudding user response：

Functions used:

Translate alter characters from text to text identified
Replace substitute value in string from one value to another.
Length counts characters in string.

Notes:

Since you indicated regular expressions may not be able to be used...
we use translate to remove all the vowels setting them to special character "~" Though I suppose we could have just used space ' '.
Translate can't be to an empty string but replace can be.
"Special" character "~" in the translate so if a column name has one of those this can result in an incorrect result to bypass the limit of it can't be blank.
replace to eliminate the speical character.
length's to ensure we removed at least 1 vowel.
Included column "Z" to show you what the translate & replace are doing.
not ideal in terms of performance because of the inability to use indexes owing to the fact we are using functions on data and limiting by that altered data.

Demo

WITH CTE AS (SELECT 'Person' "Table name", 'identification' "Column name" from dual union all
SELECT 'Person','name' from dual union all
SELECT 'Person','last_name' from dual union all
SELECT 'City','continent' from dual union all
SELECT 'City','country' from dual union all
SELECT 'City','name' from dual union all
SELECT 'City','Strength' from dual)

SELECT "Table name", "Column name", replace(translate("Column name",'aeiou','~'),'~','') z
FROM CTE
WHERE length("Column name")-length(replace(translate("Column name",'aeiou','~'),'~',''))>=2
  and length("Column name")>8

Giving us:

 ------------ ---------------- --------- 
| Table name |  Column name   |    Z    |
 ------------ ---------------- --------- 
| Person     | identification | dntfctn |
| Person     | last_name      | lst_nm  |
| City       | continent      | cntnnt  |
 ------------ ---------------- ---------

CodePudding user response：

I'm sure the regex could be a bit more elegant, but something like the following would get you in the ballpark:

SELECT * FROM yourtable WHERE regexp_like(ColumnName, '^.*[aeiou] .*[aeiou] .*$', 'i') and LENGTH(ColumnName) > 8

CodePudding user response：

Here's one way: compare the length of the word to the length of the word after the vowels are removed.

with data as (   
  select 'outLOOK' as word from dual union all    
  select 'today' as word from dual union all    
  select 'help' as word from dual    
)   
  SELECT  word
FROM    data
WHERE    LENGTH (word)
      - NVL ( LENGTH ( TRANSLATE ( word
                           , 'xAEIOUaeiou'
                 , 'x'
                 )
             )
        , 0
        ) >= 2
;

with data as (   
  select 'outLOOK' as word from dual union all    
  select 'today' as word from dual union all    
  select 'help' as word from dual    
)   
SELECT  word
FROM    data
WHERE    REGEXP_LIKE ( word
            , '[AEIOUaeiou].*[AEIOUaeiou]'
            );

This searches for

a vowel
any number of characters, 0 or more
another vowel.