CODEWITHSUNDEEP
sqloracle-database
Luigi Taormina
Luigi Taormina

Reputation: 19

How do I extract consonants from a string field?

How do I extract only the consonants from a field in records that contain names?

For example, if I had the following record in the People table:

Field Value
Name Richard

How could I extract only the consonants in "Richard" to get "R,c,r,d"?

Upvotes: 0

Views: 664

Answers (3)

user5683823
user5683823

Reputation:

If you mean "how can I remove all vowels from the input" so that 'Richard' becomes 'Rchrd', then you can use the translate function as Boneist has shown, but with a couple more subtle additions.

First, you can completely remove a character with translate, if it appears in the second argument and it doesn't have a corresponding "translate to" character in the third argument.

Second, alas, if the third (and last) argument to translate is null the function returns null (and the same if the last argument is the empty string; there is a very small number of instances where Oracle does not treat the empty string as null, but this is not one of them). So, to make the whole thing work, you need to add an extra character to both the second and the third argument - a character you do NOT want to remove. It may be anything (it doesn't even need to appear in the input string), just not one of the characters to remove. In the illustration below I use the period character (.) but you can use any other character - just not a vowel.

Pay attention too to upper vs lower case letters. Ending up with:

with
  sample_inputs (name) as (
    select 'Richard' from dual union all
    select 'Aliosha' from dual union all
    select 'Ai'      from dual union all
    select 'Ng'      from dual
  )
select name, translate(name, '.aeiouAEIOU', '.') as consonants
from   sample_inputs
;

NAME      CONSONANTS
-------   ----------
Richard   Rchrd  
Aliosha   lsh    
Ai               
Ng        Ng

Upvotes: 2

Boneist
Boneist

Reputation: 23578

You can easily do this with the translate() function, e.g.:

WITH people AS (SELECT 'Name' field, 'Richard' val FROM dual UNION ALL
                SELECT 'Name' field, 'Siobhan' val FROM dual)
SELECT field, val, TRANSLATE(val, 'aeiou', ',,,,,') updated_val
FROM   people;

FIELD VAL     UPDATED_VAL
----- ------- -----------
Name  Richard R,ch,rd
Name  Siobhan S,,bh,n

The translate function simply takes a list of characters and - based on the second list of characters, which defines the translation - translates the input string.

So in the above example, the a (first character in the first list) becomes a , (first character in the second list), the e (second character in the first list) becomes a , (second character in the second list), etc.

N.B. I really, really hope your key-value table is just a made-up example for the situation you're trying to solve, and not an actual production table; in general, key-value tables are a terrible idea in a relational database!

Upvotes: 0

BobbyW
BobbyW

Reputation: 56

Should be able to string a couple replace functions together

Select replace(replace(Value, 'A', ''), 'E', '')),...etc

Upvotes: 0

Related Questions