CODEWITHSUNDEEP
sqloracle-database
Alan Gallagher
Alan Gallagher

Reputation: 3

SQL - Remove characters after third occurrence of a comma

I have a field of authors names all separated by commas, but I only want to be able to select the first three names that appear regardless of how many overall appear in the field; I've got the below code but it isn't working for me:

select left(AUTHORS,charindex(',',AUTHORS,charindex(',',AUTHORS,charindex(',',AUTHORS)+1)+1)-1)

I get the error

ORA-00904: "LEFT": invalid identifier

Any ideas on where I'm going wrong would be appreciated.

Thank you

Upvotes: 0

Views: 289

Answers (2)

VBoka
VBoka

Reputation: 9083

This will give you the result you asked:

select NVL(SUBSTR(AUTHORS, 1, INSTR(AUTHORS,',',1,3) -1), AUTHORS)
from mytable;

Also in case you have only 2 author names this will return the column value.

Here is a small demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In Oracle, you can use regexp_replace():

select regexp_replace(authors, '^([^,]+,[^,]+,[^,]+).*$', '\1')

Upvotes: 1

Related Questions