'How do I extract the first 3 consonates from a string field SQL?

how can I extract from a field in records that contain names only the first 3 consonants and if a name does not have 3 consonants it adds the first vowel of the name?

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

Field:Name
VALUE:Richard result=> RCH

FIELD:Name
VALUE:Paul  result=> PLA
sqloracleplsql


Solution 1:[1]

Here's one option; read comments within code.

Sample data:

SQL> with test (name) as
  2    (select 'Richard' from dual union all
  3     select 'Paul'    from dual
  4    ),

Query begins here:

  5  temp as
  6    -- val1 - consonants; val2 - vowels
  7    (select
  8       name,
  9       translate(upper(name), '#AEIOU', '#') val1,
 10       translate(upper(name), '#BCDFGHJKLMNPQRSTWXYZ', '#') val2
 11     from test
 12    )
 13  -- finally: if there are enough consonants (val1's length is >= 3), return the first 3
 14  -- letters (that's WHEN).
 15  -- Otherwise, add as many vowels as necessary (that's what ELSE does)
 16  select name,
 17    case when length(val1) >= 3 then substr(val1, 1, 3)
 18         else val1 || substr(val2, 1, 3 - length(val1))
 19    end result
 20  from temp;

NAME    RESULT
------- --------------
Richard RCH
Paul    PLA

SQL>

Solution 2:[2]

Just for fun using regexp:

select
  name
 ,substr(
    regexp_replace(
       upper(name)
      ,'^([AEIOU]*)([^AEIOU]*)([AEIOU]*)([^AEIOU]*)([AEIOU]*)([^AEIOU]*).*'
      ,'\2\4\6\1\3\5'
    ),1,3) as result
from test;
  • ([AEIOU]*) - is a group of vowels, 0 or more characters
  • ([^AEIOU]*) - is a group of not-vowels (or consonants in this case), 0 or more characters
  • so this regexp looks for a pattern (vowels1)(consonants1)(vowels2)(consonants2)(vowels3)(consonants3) and reorders it to (consonants1)(consonants2)(consonants3)(vowels1)(vowels2)(vowels3)
  • then we just take first 3 characters from the reordered string

Full test case:

with test (name) as
   (select 'Richard' from dual union all
    select 'Paul'    from dual union all
    select 'Annete'  from dual union all
    select 'Anny'    from dual union all
    select 'Aiua'    from dual union all
    select 'Isaiah'  from dual union all
    select 'Sue'     from dual
   )
select
  name
 ,substr(
    regexp_replace(
       upper(name)
      ,'^([AEIOU]*)([^AEIOU]*)([AEIOU]*)([^AEIOU]*)([AEIOU]*)([^AEIOU]*).*'
      ,'\2\4\6\1\3\5'
    ),1,3) as result
from test;

NAME    RESULT
------- ------------
Richard RCH
Paul    PLA
Annete  NNT
Anny    NNY
Aiua    AIU
Isaiah  SHI
Sue     SUE

7 rows selected.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot
Solution 2

Related Questions