CODEWITHSUNDEEP
sqlstringpostgresqlpattern-matchingdelimiter
Gismo Ranas
Gismo Ranas

Reputation: 6442

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.

Upvotes: 20

Views: 23294

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656992

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:

Upvotes: 35

rchacko
rchacko

Reputation: 2119

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

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

Upvotes: -1

vol7ron
vol7ron

Reputation: 42109

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':.*', '');

Upvotes: 1

Related Questions