CODEWITHSUNDEEP
postgresql
eljusticiero67
eljusticiero67

Reputation: 2482

truncate field after nth instance of a character postgresql

I have fields that look like this:

id     field
1      aaa,bbb,ccc,ddd,eee,ffff

I am looking to select field up till the third instance of the comma. It would look something like this:

aaa,bbb,ccc

Is this possible in postgresql?

Upvotes: 0

Views: 1085

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

I would prefer to use a regular expression here. The regex which will match your fixed pattern is:

^(\w+,\w+,\w+).*$

Using regexp_matches, this becomes:

select regexp_matches(field, E'^(\\w+,\\w+,\\w+).*$')
from yourTable

Note: I tested this query on my local Postgres and it runs fine, q.v. this screen capture

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

First, you should not be storing comma delimited values in a string field. But that said, one method uses split_part():

select split_part(field, ',', 1) || ',' || split_part(field, ',', 2) || ',' || split_part(field, ',', 3)

Upvotes: 1

Related Questions