-
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
Jeff Hunter
-
Actual query
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
Last edited by ssmith001; 09-23-2003 at 04:11 PM.
-
try this:
select x ,
rpad(replace(y,'#',''),9) y,
rpad(replace(z,'#',''),6) z
from xyz
/
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|