This discussion is archived
10 Replies Latest reply: Dec 11, 2012 8:28 AM by AnimeshTripathi RSS

Replace each third comma with ||

AnimeshTripathi Newbie
Currently Being Moderated
Hi All,

My Requirement is like this:
In a column i need to replace every third comma(,) with || symbol.

eg:
1,Animesh,1234,2,Tripathi,4321,3,Oracle,2345.....(and so on)

Desired output:
1,Animesh,1234||2,Tripathi,4321||3,Oracle,2345....
Please help me out.
-
Regards
Animesh

Edited by: Animesh Tripathi on Dec 11, 2012 2:22 AM
  • 1. Re: Replace each third comma with ||
    AlexandarVu Newbie
    Currently Being Moderated
    If your file is in one line, then try something like this(this is for Linux)

    sed 's/\(\([^,]*,\)\{2\}[^,]*\),/\1\||/g' data.txt
  • 2. Re: Replace each third comma with ||
    AnimeshTripathi Newbie
    Currently Being Moderated
    Hi Alexander,

    Sorry for the confusion.

    That is not in a form of a file.
    I have those data in a Column of a table.

    -
    Regards
    Animesh
  • 3. Re: Replace each third comma with ||
    kendenny Expert
    Currently Being Moderated
    SQL> WITH a AS (
      2  SELECT '1,Animesh,1234,2,Tripathi,4321,3,Oracle,2345' col1
      3  FROM dual)
      4  SELECT regexp_replace(a.col1,'([^,]*,[^,]*,[^,]*),','\1||') from a;
     
    REGEXP_REPLACE(A.COL1,'([^,]*,
    ----------------------------------------------
    1,Animesh,1234||2,Tripathi,4321||3,Oracle,2345
  • 4. Re: Replace each third comma with ||
    Jitendra Pro
    Currently Being Moderated
    Hi Animesh,

    Try to use regex.
    try the below code.
    SET SERVEROUTPUT ON
    DECLARE
    v_file VARCHAR2(100) :=  '1,Animesh,1234,2,Tripathi,4321,3,Oracle,2345';
      v_file1 VARCHAR2(100);
    BEGIN
      SELECT REGEXP_REPLACE (v_file, '(([^,]*,){2}[^,]*)(,)', '\1||')
      INTO v_file1
      FROM DUAL;
      DBMS_OUTPUT.PUT_LINE(v_file1);
    END;
    I found above solution from one of the thread.

    you can find some details about regex_replace in below given link.
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

    Regards,
    Jitendra
  • 5. Re: Replace each third comma with ||
    LPS Journeyer
    Currently Being Moderated
    Duplicate post...Just 2 hours before it was posted by the same person

    Replace comma with || in a column
  • 6. Re: Replace each third comma with ||
    AnimeshTripathi Newbie
    Currently Being Moderated
    Hi Jitendra,Kendenny,

    Thanks for the Answer
    It was really Needed for me.

    -
    Regards
    Animesh
    ----------------------------------------------------------------------------
    Hi LPS,

    Sorry for duplicating the post.
    The reason i duplicated this is because, earlier i did not get any proper answer for this and I thought of replicating this
    to another community.

    -
    Animesh
  • 7. Re: Replace each third comma with ||
    chris227 Guru
    Currently Being Moderated
    with data as (
    select '1,Animesh,1234,2,Tripathi,4321,3,Oracle,2345,1,Animesh,1234,2,Tripathi,4321,3,Oracle,2345' s from dual
    )
    -- assumption no comma at start or end
    select
      regexp_replace (
        s
       ,'([^,]+,[^,]+,)([^,]+),'
       ,'\1\2||'
      )
    from data 
    
    REGEXP_REPLACE(S,'([^,]+,[^,]+,)([^,]+),','\1\2||')
    1,Animesh,1234||2,Tripathi,4321||3,Oracle,2345||1,Animesh,1234||2,Tripathi,4321||3,Oracle,2345
    
    Ah, now i got the shorter version
    select
      regexp_replace (
        s
       ,'(([^,]+,){2,2}[^,]+),'
       ,'\1||'
      )
    from data 
    Edited by: chris227 on 11.12.2012 06:21
    shorter version

    Edited by: chris227 on 11.12.2012 06:24
    finally we had that nearly (last group for comma isnt necessary
  • 8. Re: Replace each third comma with ||
    AlbertoFaenza Expert
    Currently Being Moderated
    Animesh Tripathi wrote:
    Hi LPS,

    Sorry for duplicating the post.
    The reason i duplicated this is because, earlier i did not get any proper answer for this and I thought of replicating this
    to another community.

    -
    Animesh
    Hi Animesh,

    you have posted again the same question to have again the same exact answer.
    I have answered you previously and you seemed also satisfied in this thread: {message:id=10739356}:
    Animesh Tripathi wrote:
    Hi Alberto,

    That was what i exactly wanted.

    Thanks for the Help.

    -
    Regards
    Animesh
    Why are you saying you did not get a proper answer?
    Did you face any problem with previous solution?

    Regards.
    Al
  • 9. Re: Replace each third comma with ||
    odie_63 Guru
    Currently Being Moderated
    Al,

    This thread has been moved from the {forum:id=732} forum, where it was first posted earlier, hence the confusion ;)
  • 10. Re: Replace each third comma with ||
    AnimeshTripathi Newbie
    Currently Being Moderated
    Hi Odie,

    Thanks for clearing Confusion between the GEEKS.
    This Question was initially posted in Export/Import/SQL Loader & External Tables.
    Then I came to know that that was not the appropriate place for my question so i replicated it on SQL and PL/SQL forum.
    -
    Regards
    Animesh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points