CODEWITHSUNDEEP
postgresql
Michael C
Michael C

Reputation: 41

String Matching Pattern in postgresql

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?

Upvotes: 3

Views: 68

Answers (1)

klin
klin

Reputation: 121889

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.

Upvotes: 1

Related Questions