Replace last comma in string - oracle sql

Posted on 2010-04-08
Medium Priority
Last Modified: 2013-12-19
I have a field named per_first_names in a table named o_persons
Here is an example of the data within it:

mark, luisa, peter
victoria, lucy
john, charlotte, janice, andrew

i'd like to replace the last comma in the string with the word 'and', so the output would be:

mark, luisa and peter
victoria and lucy
john, charlotte, janice and andrew

is this possible using sql without a function? thankyou
Question by:tonMachine100
  • 2
LVL 22

Expert Comment

by:Helena Marková
ID: 30109173
SELECT substr('mark, luisa, peter',1,instr('mark, luisa, peter', ',' ,-1)-1)||'and '||
substr('mark, luisa, peter',instr('mark, luisa, peter', ',' ,-1)+1) from dual;
LVL 22

Accepted Solution

Helena Marková earned 500 total points
ID: 30109593
Here is a little correction:
SELECT substr('mark, luisa, peter',1,instr('mark, luisa, peter', ',' ,-1)-1)||' and '||
substr('mark, luisa, peter',instr('mark, luisa, peter', ',' ,-1)+1) from dual;
LVL 74

Assisted Solution

sdstuber earned 500 total points
ID: 30112883
if you upgrade to 10g or later it gets even simpler...

select  regexp_replace(per_first_names,'(,)([^,]*$)',' and\2') from o_persons

Author Closing Comment

ID: 31712252
Thanks both

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question