Substring should return Distinct values

Question

I have a name column like this:

Name
------
Roger
RogerMan
Roger That

I need a query which would return distinct initials from the column. For example, in this case I want to return:

Name
-----
R
RM
RT

I have tried a number of approaches for this: 1. CASE statement (works perfectly by far). 2. STRCMP 3. SUBSTRING

However, I am looking for a more dynamic approach using functions in Postgres.

Garfield, in your example, you will have two rows, R and RT because RogerMan being one word will just return R as its initial. — zip, Feb 12 '20 at 16:43

score 3 · Accepted Answer · answered Feb 12 '20 at 16:44

You could use regexp_replace() to remove all non capital alphabetic characters from the string, like so:

 regexp_replace(name, '[^A-Z]', '', 'g')

Demo on DB Fiddle:

with t(name) as (
    select 'Roger'
    union all select 'RogerMan'
    union all select 'Roger That'
)
select name, regexp_replace(name, '[^A-Z]', '', 'g') initials from t

name       | initials
:--------- | :-------
Roger      | R       
RogerMan   | RM      
Roger That | RT

Substring should return Distinct values

1 Answers1