DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Function to replace text

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2003
    Posts
    100
    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

  5. #5
    Join Date
    Aug 2003
    Posts
    100

    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.

  6. #6
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    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
  •  


Click Here to Expand Forum to Full Width