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

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

CodePudding user response：

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>

CodePudding user response：

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;

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.