String Matching Pattern in postgresql

Question

I have a long_name column. I need to get short name for this column. Short name is made up of the first character after a "_" in the long name.

For example: long_name: '_Michael_Smith' should result in 'MS' short_name long_name: '_Michael_John_Smith' should result in 'MJS' short_name

I can get the first character using: substring(long_name from position('_' in long_name)+1 for 1) as short_name.

How can I get the rest of the characters in a query?

Answer 1

Use regexp_replace():

with example(long_name) as (
    values
        ('_Michael_Smith'),
        ('_Michael_John_Smith') 
    )
select 
    long_name, 
    regexp_replace(long_name, '_(.)[^_]+', '\1', 'g') short_name
from example;

      long_name      | short_name 
---------------------+------------
 _Michael_Smith      | MS
 _Michael_John_Smith | MJS
(2 rows)

Read: POSIX Regular Expressions.