Cut string after first occurrence of a character

sql string postgresql pattern-matching delimiter
Gismo Ranas picture Gismo Ranas · Apr 8, 2015 · Viewed 12.5k times · Source

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

Erwin Brandstetter picture Erwin Brandstetter · Apr 8, 2015

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:

Related questions

Error: invalid input syntax for integer: ""

I have this table tbl_buku: id_buku judul_buku tahun_buku 1 Bioogi 2010 2 Fisika 2010 3 Informatika 2012 4 Kimia 2012 I use query like this, but I am getting an error: select case when t1.tahun_buku=t2.tahun_buku then '' else t1.…

Read More
UPDATE and REPLACE part of a string

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column. Example of Table: ID Value --------------------------------- 1 c:\temp\123\abc\111 2 c:\temp\123\abc\222 3 c:\temp\123\abc\333 4 c:\temp\123\…

Read More
Remove all spaces from a string in SQL Server

What is the best way to remove all spaces from a string in SQL Server 2008? LTRIM(RTRIM(' a b ')) would remove all spaces at the right and left of the string, but I also need to remove the …

Read More