CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Jeromy French
Jeromy French

Reputation: 12121

Replace last occurrence of substring

Given this string:

.ABC,.123,.FGH,.QDG

How can I most-efficiently use PL/SQL to output (note the ", and"):

abc, 123, fgh, and qdg

So far I've got LOWER(REPLACE('.ABC,.123,.FGH,.QDG', '.', ' ')), which yields

abc, 123, fgh, qdg

How do I get that "and" after "fgh,", but before "qdg" ("fgh, and qdg")? Must I use INSTR() and SUBSTR(), or is there a function that can just replace the last space with " and "?

Upvotes: 2

Views: 13520

Answers (2)

xQbert
xQbert

Reputation: 35343

Building on what you used already... use regexp_replace with instr

with cte as(
select LOWER(REPLACE('.ABC,.123,.FGH,.QDG', '.', ' ')) as val from dual)
select regexp_replace(val,' ',' and ',instr(val ,' ',-1))
from cte;

or regexp_replace(val,', ',' and ',instr(val ,', ',-1)) if you want to get rid of the last comma too.

enter image description here

Upvotes: 6

Tony Andrews
Tony Andrews

Reputation: 132720

This will do it:

select substr(text, 1, last_space_pos) || 'and' || substr(text, last_space_pos) new_text
from
(select text, instr(text, ' ', -1) last_space_pos
 from
 (select LOWER(REPLACE('.ABC,.123,.FGH,.QDG', '.', ' ')) text from dual)
);

The function call instr(text, ' ', -1) finds the position of the last space, and the substr calls break the string at that point so that we can insert 'and'. The use of nested select statements avoids having to repeat any complex expressions.

Upvotes: 1

Related Questions