Cut a string after the 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 : in and will not work if there is no : in the line.

Answer 1

Use split_part() :

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

Returns the entire string if the delimiter does not exist. And it's just to get a second or third part, etc.

Significantly faster than regular expression functions. And since we have a fixed delimiter, we don’t need the magic of regular expressions.

on this topic:

• Separate column data separated by commas into additional columns

Answer 2

regexp_replace () can be overloaded for what you need, but it also gives the added benefit of a regular expression. For example, if strings use multiple delimiters.

Usage example:

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

Answer 3

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

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