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
– zip, Commented Feb 12, 2020 at 16:43

GMB · Accepted Answer · 2020-02-12 16:44:17Z

3

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

answered Feb 12, 2020 at 16:44

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Substring should return Distinct values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related