10 Replies Latest reply: Dec 11, 2012 10:28 AM by Animesh Tripathi RSS

    Replace each third comma with ||

    Animesh Tripathi
      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
          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 ||
            Animesh Tripathi
            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
              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
                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
                  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 ||
                    Animesh Tripathi
                    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
                      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
                        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
                          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 ||
                            Animesh Tripathi
                            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