Gismo Ranas Gismo Ranas - 2 years ago 105
SQL Question

Cut string after first occurrence of a character

I have strings like 'keepme:cutme' or 'string-without-separator' which should become respectively 'keepme' and 'string-without-separator'. Can this be done in PostgreSQL? I tried:

select substring('first:last' from '.+:')

But this leaves the
in and won't work if there is no
in the string.

Answer Source

Use split_part():

SELECT split_part('first:last', ':', 1) AS first_part

Returns the whole string if the delimiter is not there. And it's simple to get the 2nd or 3rd part etc.

Substantially faster than functions using regular expression matching. And since we have a fixed delimiter we don't need the magic of regular expressions.


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download