Gismo Ranas Gismo Ranas - 11 months ago 59
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.

Related:

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