1 2 3 Previous Next 30 Replies Latest reply on Jun 3, 2012 3:46 PM by chris227

    How to concatnate the string in SQL or PL/SQL?

    634151
      Hi,

      I have the value like A.B and i have another value 1.0.0.C.D

      Is it possible to replace 0.0 with A.B value in SQL or PL/SQL?

      The string should be 1.A.B.C.D

      Thanks
        • 1. Re: How to concatnate the string in SQL or PL/SQL?
          vijayrsehgal-Oracle
          something like below could help you, but this is with reference to your values, you need to enhance it further if you have data coming from table.
          select replace('1.0.0.C.D','.0.0','.a.b') from dual
          Regards,
          Vijay

          Edited by: user10302525 on May 18, 2012 3:36 AM
          • 2. Re: How to concatnate the string in SQL or PL/SQL?
            Solomon Yakobson
            Use REPLACE:
            SQL> select  replace('1.0.0.C.D','0.0','A.B')
              2    from  dual
              3  /
            
            REPLACE('
            ---------
            1.A.B.C.D
            
            SQL> 
            SY.
            • 3. Re: How to concatnate the string in SQL or PL/SQL?
              Vivek L
              user212310 wrote:
              Hi,

              I have the value like A.B and i have another value 1.0.0.C.D

              Is it possible to replace 0.0 with A.B value in SQL or PL/SQL?

              The string should be 1.A.B.C.D

              Thanks
              Sorry, but your question is too vague to answer.
              What values to be replaced? 0.0 in all cases?
              How do you get the values ("A.B") to be replaced with? is it A.B in all cases?
              A simple replace can do what you are asking for :
              SQL> select replace('1.0.0.C.D','0.0','A.B') from dual;
              
              REPLACE('
              ---------
              1.A.B.C.D
              But does this meet your requirements?
              Please read the forum FAQ {message:id=9360002}
              and provide all necessary details along with a sample test case
              Also, always provide your 4 digit Oracle database version
              • 4. Re: How to concatnate the string in SQL or PL/SQL?
                634151
                hi,

                Thanks for all your response.

                The value 0.0 is not stable. Sometimes it will be different too. This 0.0 value is stored in the same table where my entire string is stored 1.0.0.C.D

                But my expectation is somewhat completcated here. I mean sometimes we need to replace the value 0.C or 1.C or 1.D also with appropriate value. We will consider that the above string is separated with comma separated value and each values are called as segments. I have another table where i can store the sequence number of the segment. In my example, the sequence number of the value 0.0 is 2,3. At the same time 0.C is 3,4 and 1.C is 1,4 and 1.D is 1,5.

                So now whatever value is, it may be 0.0 or 0.C or 1.C or 1.D we have to repalce with the appropriate values. For example.


                1.0.0.C.D --> 0.0 --> 1.A.B.C.D
                1.0.0.C.D --> 0.C --> 1.0.A.B.D
                1.0.0.C.D --> 1.C --> A.0.0.B.D
                1.0.0.C.D --> 1.D --> A.0.0.C.B
                1.0.0.C.D --> C.D --> 1.0.0.A.B



                Thanks
                • 5. Re: How to concatnate the string in SQL or PL/SQL?
                  Etbin
                  Maybe
                  replace(replace(your_string,substr(to_replace,1,2),'A.'),substr(to_replace,2),'.B')
                  Regards

                  Etbin
                  • 6. Re: How to concatnate the string in SQL or PL/SQL?
                    Biju Das
                    Forget what you have written here.
                    Gather the requirement well, what is the objective? Define the set of all rules to meet the requirement and then try to solve the problem. If failed, post your question here describing your requirement well. I am having hard time here understanding your requirement. It seems you are trying to solve something without having enough information.

                    Regards
                    Biju

                    Edited by: biju2012 on May 19, 2012 3:40 AM
                    • 7. Re: How to concatnate the string in SQL or PL/SQL?
                      634151
                      Hi Biju,

                      There is no other way to explain here. I explained fully with clear examples. I cannot explain other than this.

                      Thanks
                      • 8. Re: How to concatnate the string in SQL or PL/SQL?
                        Solomon Yakobson
                        user212310 wrote:

                        There is no other way to explain here. I explained fully with clear examples. I cannot explain other than this.
                        Assuming all strings have 5 dot separated parts:

                        First table:
                        SQL> select  *
                          2    from  tbl1
                          3  /
                        
                        STR       VAL
                        --------- ---
                        1.0.0.C.D 0.0
                        1.0.0.C.D 0.C
                        1.0.0.C.D 1.C
                        1.0.0.C.D 1.D
                        1.0.0.C.D C.D
                        
                        SQL> 
                        Second table:
                        SQL> select  *
                          2    from  tbl2
                          3  /
                        
                        VAL SEQ
                        --- ---
                        0.0 2,3
                        0.C 3,4
                        1.C 1,4
                        1.D 1,5
                        C.D 4,5
                        
                        SQL> 
                        Solution:
                        with t1 as (
                                    select  val,
                                            seq,
                                            lvl,
                                            case
                                              when ',' || seq || ',' like '%,' || lvl || ',%' then 1
                                            end ind
                                      from  tbl2,
                                            (
                                             select  level lvl
                                               from  dual
                                               connect by level <= 5
                                            )
                                   ),
                             t2 as (
                                    select  val,
                                            seq,
                                            lvl,
                                            case ind
                                              when 1 then chr(ascii('A') + sum(ind) over(partition by val order by lvl) - 1)
                                            end new
                                      from  t1
                                   )
                        select  str,
                                tbl1.val,
                                seq,
                                rtrim(xmlagg(xmlelement(e,nvl(new,regexp_substr(str,'[^.]+',1,lvl)),'.').extract('//text()') order by lvl),'.') new_str
                          from  tbl1,
                                t2
                          where t2.val = tbl1.val
                          group by str,
                                   tbl1.val,
                                   seq
                        /
                        
                        STR       VAL SEQ NEW_STR
                        --------- --- --- ----------
                        1.0.0.C.D 0.0 2,3 1.A.B.C.D
                        1.0.0.C.D 0.C 3,4 1.0.A.B.D
                        1.0.0.C.D 1.C 1,4 A.0.0.B.D
                        1.0.0.C.D 1.D 1,5 A.0.0.C.B
                        1.0.0.C.D C.D 4,5 1.0.0.A.B
                        
                        SQL> 
                        SY.
                        • 9. Re: How to concatnate the string in SQL or PL/SQL?
                          634151
                          Hi Solomon,

                          Thanks for the reply. I have changed the table values bit. It will looks like below.
                          TB1 table
                          
                          STR            VAL      NAME       EXPECTED_STR
                          ---------         ---        --------       ------------------------
                          1.0.0.C.D     A.B       PH           1.A.B.C.D (Replace segment 2 and 3 with A and B)
                          1.0.0.C.D     A.B       PH1         1.0.A.B.D (Replace segment 3 and 4 with A and B)
                          1.0.0.C.D     A.B       PH2         A.0.0.B.D (Replace segment 1 and 4 with A and B)
                          1.0.0.C.D     A.B       PH3         A.0.0.C.B (Replace segment 1 and 5 with A and B)
                          1.0.0.C.D     A.B       PH4         1.0.0.A.B (Replace segment 4 and 5 with A and B)
                          
                          TB2 table
                          
                          NAME      SEQ
                          -------       ------
                          PH           2,3
                          PH1         3,4
                          PH2         1,4
                          PH3         1,5
                          PH4         4,5
                          From the above struture for all the PH name it should be 2,3 segments and for PH1 it is 3,4 needs to be replaced.

                          Thanks
                          • 10. Re: How to concatnate the string in SQL or PL/SQL?
                            Solomon Yakobson
                            with t1 as (
                                        select  name,
                                                seq,
                                                lvl,
                                                case
                                                  when ',' || seq || ',' like '%,' || lvl || ',%' then 1
                                                end ind
                                          from  tbl2,
                                                (
                                                 select  level lvl
                                                   from  dual
                                                   connect by level <= 5
                                                )
                                       ),
                                 t2 as (
                                        select  name,
                                                seq,
                                                lvl,
                                                case ind
                                                  when 1 then sum(ind) over(partition by name order by lvl)
                                                end ind
                                          from  t1
                                       )
                            select  str,
                                    tbl1.name,
                                    val,
                                    seq,
                                    rtrim(xmlagg(xmlelement(e,nvl(regexp_substr(val,'[^.]+',1,ind),regexp_substr(str,'[^.]+',1,lvl)),'.').extract('//text()') order by lvl),'.') new_str
                              from  tbl1,
                                    t2
                              where t2.name = tbl1.name
                              group by str,
                                       tbl1.name,
                                       val,
                                       seq
                            /
                            
                            STR       NAME  VAL SEQ NEW_STR
                            --------- ----- --- --- --------------------
                            1.0.0.C.D PH    A.B 2,3 1.A.B.C.D
                            1.0.0.C.D PH1   A.B 3,4 1.0.A.B.D
                            1.0.0.C.D PH2   A.B 1,4 A.0.0.B.D
                            1.0.0.C.D PH3   A.B 1,5 A.0.0.C.B
                            1.0.0.C.D PH4   A.B 4,5 1.0.0.A.B
                            
                            SQL> 
                            SY.
                            • 11. Re: How to concatnate the string in SQL or PL/SQL?
                              Solomon Yakobson
                              Actually, MODEL solution might be simpler:
                              select  str,
                                      name,
                                      val,
                                      seq,
                                      new_str
                                from  tbl1,
                                      tbl2
                                where tbl2.name = tbl1.name
                                model
                                  partition by(row_number() over(order by tbl1.rowid) p)
                                  dimension by(1 d)
                                  measures(str,tbl1.name,val,seq,str new_str,0 ind)
                                  rules iterate(5)
                                    (
                                     ind[1]     = case
                                                    when ',' || seq[1] || ',' like '%,' || (iteration_number + 1) || ',%' then ind[1] + 1
                                                    else ind[1]
                                                  end,
                                     new_str[1] = case
                                                    when ',' || seq[1] || ',' like '%,' || (iteration_number + 1) || ',%' then regexp_replace(new_str[1],'[^.]+',regexp_substr(val[1],'[^.]+',1,ind[1]),1,iteration_number + 1)
                                                    else new_str[1]
                                                  end
                                   )
                                order by name
                              /
                              
                              STR       NAME  VAL SEQ NEW_STR
                              --------- ----- --- --- ----------
                              1.0.0.C.D PH    A.B 2,3 1.A.B.C.D
                              1.0.0.C.D PH1   A.B 3,4 1.0.A.B.D
                              1.0.0.C.D PH2   A.B 1,4 A.0.0.B.D
                              1.0.0.C.D PH3   A.B 1,5 A.0.0.C.B
                              1.0.0.C.D PH4   A.B 4,5 1.0.0.A.B
                              
                              SQL> 
                              SY.
                              • 12. Re: How to concatnate the string in SQL or PL/SQL?
                                634151
                                Hi Soloman,

                                It is awesome and working as expected. I have never known about this MODEL clause. Could you please provide some link to understand about this?

                                Also please let me know about the performance if i run the query for large volume of records? I am not getting the below sections and explain me how it will work?

                                dimension by(1 d)
                                rules iterate(6)

                                Thanks
                                • 13. Re: How to concatnate the string in SQL or PL/SQL?
                                  Solomon Yakobson
                                  user212310 wrote:

                                  It is awesome and working as expected. I have never known about this MODEL clause. Could you please provide some link to understand about this?
                                  MODEL clause was introduced in 10g. Same as analytic functions MODEL works over resultset which it interplets as a spreadsheet. But unlike analytic functions it allows to base cell values based on other cell(s) and also allows iterations. I admit, it could be better documented with more explanations and examples, but again these are manuals and not tutorials. I don't follow it much, so I don't know any good tutorials on MODEL. Now explanation of this MODEL solution:

                                  1. PARTITION BY splits all rows into partitions. Here we want to partition by TBL1.STR. In fact, I overcomplicated it - all I needed was:
                                  partition by(tbl1.rowid p)
                                  2. DIMENSION BY. It uniquely identifies each cell within partition. Since TBL1 & TBL2 join is one-to-one, each partition has just one row, therefore I used:
                                  dimension by(1 d)
                                  3. MEASURES defines spreadsheet columns.

                                  4. RULES sets formulas how to calculate cell values. Here I uses iterate rules (5 iterations) since I know upfront each str has 5 parts. So we iterate over str 5 times and if needed replace corresponding part with proper value from val. Cell ind rule checks if current current part (we use iteration number + 1 since iterations are numbered starting zero) is present in seq and therefore needs to be replaced and if it does, and which part of val it needs to be replaced with. Cell new_str rule does, if needed, part replacement. So, for example, new_str[1]='1.0.0.C.D', name[1]='PH', seq[1]='2,3' and val[1]='A.B'. First iteration checks if 1 is preset in seq[1]. Since it is not present, ind[1] remains 0. Same way since 1 is not present in seq[1], new_str[1] remains '1.0.0.C.D'. Now we do second iteration. Now, 2 is present in seq[1], therefore ind[1] is assigned value 1. Same way since 2 is present in seq[1], part 2 of new_str[1]='1.0.0.C.D' is replaced with part 1 (ind[1]=1) of val[1] (which is 'A') and new_str[1] becomes '1.A.0.C.D'. Now we do third iteration, and again 3 is present in seq[1], ind[1] becomes 2 and new_str becomes '1.A.B.C.D'. Iterations 4 and 5 do not change new_str since they are not present in seq[1]. Then same logic is applied to next partition. I hope you get the picture.

                                  Performace-wise, you'll have to test it yourself. One thing I can tell, your design could be changed to improve performance. If TBL2 would store multiple rows per name, splitting seq into separate items. You could also split table TBL1 into TBL1A and TBL1B where TBL1A would store str and name and TBL1B would store str, name and separate parts of val.

                                  SY.
                                  • 14. Re: How to concatnate the string in SQL or PL/SQL?
                                    chris227
                                    model without regexp and less iterations:
                                    with t1 as (
                                    select '1.0.0.C.D' str,'A.B' val, 'PH' name from dual union all
                                    select '1.0.0.C.D','A.B','PH1' name from dual union all
                                    select '1.0.0.C.D','A.B','PH2' name from dual union all
                                    select '1.0.0.C.D','A.B','PH3' name from dual union all
                                    select '1.0.0.C.D','A.B','PH4' name from dual)
                                    , t2 as (
                                    select 'PH' name,'2,3' seq from dual union all
                                    select 'PH1','3,4' from dual union all
                                    select 'PH2','1,4' from dual union all
                                    select 'PH3','1,5' from dual union all
                                    select 'PH4','4,5' from dual )
                                    
                                    select
                                    str
                                    from (select str,val,seq,t2.name name from t1,t2
                                    where
                                    t1.name=t2.name)
                                    model
                                    dimension by (name n)
                                    measures(str,seq,val)
                                    rules iterate(2) ( -- 2 = number of numbers in seq
                                    str[any]=
                                    substr(
                                      str[cv()]
                                    , 1
                                    , substr(seq[cv()],1,1)*2-2
                                    )||
                                    substr(val[cv()],((iteration_number+1)*2-1),1)||
                                    substr(
                                      str[cv()]
                                    , substr(seq[cv()],1,1)*2
                                    )
                                    ,seq[any]=substr(seq[cv()],((iteration_number+1)*2+1),1)
                                    )
                                    
                                    STR
                                    1.A.B.C.D
                                    1.0.A.B.D
                                    A.0.0.B.D
                                    A.0.0.C.B
                                    1.0.0.A.B
                                    1 2 3 Previous Next