Removing vowels in the column

Tools & Utilities
Enthusiast

Removing vowels in the column

I have to concatenate first name, last name and zip code.

The concatenation should not have vowels"aAeEiIoOuU" and any special characters.

I have the logic but it's removing only single character (U).

select uid_id, case when position('' in uid_id) > 1 then (substr(uid_id,1,position('U' in uid_id) - 1)) || '' || substr(uid_id,position('U' in uid_id) + character_length('U'))
else uid_id end from cust.customer
6 REPLIES
Junior Contributor

Re: Removing vowels in the column

You shouldn't try that without a UDF, because the code will look like:

case when position(substring(first_name from 1 for 1) in 'bcdfghjklmnpqrstvwxyz') > 0
then substring(first_name from 1 for 1) else '' end ||
case when position(substring(first_name from 2 for 1) in 'bcdfghjklmnpqrstvwxyz') > 0
then substring(first_name from 2 for 1) else '' end ||
...
and so on checking each character seperately.

You could write a simple UDF for that purpose or use the existing Oracle UDF oTranslate:
otranslate(col, '.' || otranslate (col,'.bcdfghjklmnpqrstvwxyz','.'), '.')

You only need that '.' when you use the original UDF which mimics Oracle's NULL/empty string bug (which treats '' as NULL), else it's

otranslate(col, otranslate (col,'bcdfghjklmnpqrstvwxyz',''), '')

This dynamically creates a string of all the characters you don't want and replaces them with ''.

Dieter
Enthusiast

Re: Removing vowels in the column

Thanks dnoeth for your reply.

I tried with otranslate and oreplace, it's not working.

Please give some other logic, to remove the vowels and special characters.
Junior Contributor

Re: Removing vowels in the column

What did you try exactly? And what didn't work?

This is the typical approach to remove all other characters when you exactly know which chars to retain.
Of course you might have to add the uppercase chars plus the digits for the zip code.

Dieter
Enthusiast

Re: Removing vowels in the column

SEL uid_id,
otranslate(uid_id, otranslate (uid_id,'MU',''), '')
FROM cust.customer where cust_prty_id = '149167';

uid_id value = CUSTPGM-15962
Junior Contributor

Re: Removing vowels in the column

SEL 'CUSTPGM-15962' as col, otranslate(col, '.' || otranslate (col,'.MU','.'), '.');

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

col otranslate(col,('.'||otranslate(col,'.MU','.')),'.')
------------- -------------------------------------------------------------
CUSTPGM-15962 UM

SEL 'CUST=üäP°%GM-159)?´62' as col,
otranslate(col, '.' || otranslate (col,'.bcdfghjklmnpqrstvwxyzBCDFGHKLMNPQRSTUVWXYZ0123456789','.'), '.');

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

col otranslate(col,('.'||otranslate(col,'.bcdfghjklmnpqrstvwxyzB
------------- -------------------------------------------------------------
CUST=üäP°%GM-159)?´62 CUSTPGM15962
New Member

Re: Removing vowels in the column

expanding on this one, as i've been all over the internet researching regex in all its flavors... i want to remove vowels, but only non-initial vowels, and only in words longer than 4 characters. initcap on the phrase lets me remove the non-initial vowels by using case sensitivity. i can get to the longer words using the positive lookahead (?=\b\w{5,}\b)\b\w*, but can't get to the lowercase vowels within only those words. halp?