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?

For performance reasons, you should probably just add a new column and determine the short name programmatically. This implies synchronizing values between the 2 columns, but the queries will probably run much faster. Another possibility is to determine the value programmatically on the fly based on query results. If you're determined to use just one column and need the short name value in the query results, you might try using regexp_replace: postgresql.org/docs/current/static/functions-matching.html — aro_tech
– aro_tech, Commented Dec 20, 2015 at 9:26

klin · Accepted Answer · 2015-12-20 10:44:18Z

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.

answered Dec 20, 2015 at 10:44

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Andomar Over a year ago

As clarification: this replaces the entire match _(.)[^_]+ with the first capture group (.)

Collectives™ on Stack Overflow

String Matching Pattern in postgresql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related