Cut string after first occurrence of a character

Question

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 1

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:

• Split comma separated column data into additional columns

Answer 2

regexp_replace() may be overload for what you need, but it also gives the additional benefit of regex. For instance, if strings use multiple delimiters.

Example use:

select regexp_replace( 'first:last', E':.*', '');

Answer 3

SQL Select to pick everything after the last occurrence of a character

select right('first:last', charindex(':', reverse('first:last')) - 1)