Function to replace text

Is there an Oracle function I can use to replace one character with another? I need to replace all occurances of # in a string with blanks.

Steve

The clue is in the question there.

Have a look at the SQL Reference m,anual at http://tahiti.oracle.com for the name of a function that might be involved in REPLACE'ing text.

Slimdave,

Maybe if I tell you what I'm trying to do, you can get a better picture. I am attempting to build a single character string that is made up of three fields concatenated together.

Field X is Char(3)
Field Y is Char(25)
Field Z is Char(10)

Before (the letters on the 1st line are positional markers):

XXXYYYYYYYYYYYYYYYYYYYYYYYYYZZZZZZZZZZZ
010#E0001K2 034

After:
XXXYYYYYYYYYYYYZZZ
010 E0001K2 034

for a total of 18 characters.

Steve

Not sure what you're asking, but here are some examples:

Code:

---

SQL> select * from xyz;

X  Y          Z
--- ---------- ----------
001 83727#828  828273
002 #2882277  282727#22
003 273727#    282#28727#

SQL> select x || y || z from xyz;

X||Y||Z
-----------------------
00183727#828828273
002#2882277282727#22
003273727#282#28727#

  1* select replace(x || ' ' || y || ' ' || z, '#', '') from xyz
SQL> /

REPLACE(X||''||Y||''||Z,'
-------------------------
001 83727828 828273
002 2882277 28272722
003 273727 28228727

---

Here's the query...

select substr(a.mcrp30,4,2)||decode(b.ibsrp8,'H',1,0)||replace(c.imaitm,'#',' ')||trim(substr(a.mcrp30,1,3)) as ITEM_CODE
from f0006 a, f4102 b, f4101 c
where a.mcmcu = b.ibmcu
and b.ibitm = c.imitm

and here's the result. Crap! I lost all the formatting, but imagine that the first line reads: 580~~100276R1~~423 (where ~ = spaces)

ITEM_CODE
----------------------------------
580 100276R1 423
580 100662 423
580 E2170R2 423
580 E2181R1 423
580 E6407R8 423
580 E6946R9 423
580 E8268R5 423
580 E9395R3 423
580 EE0142R2 423
260PGM1002C 408
260PTS1000A 408

What I need for it to look like is this (18 chars total):

580 100276R1 423

Steve

try this:
select x ,
rpad(replace(y,'#',''),9) y,
rpad(replace(z,'#',''),6) z
from xyz
/