1 2 Previous Next 24 Replies Latest reply: Nov 15, 2012 6:41 AM by Solomon Yakobson Go to original post RSS
      • 15. Re: Changing alternative letters into upper case
        jeneesh
        Solomon Yakobson wrote:
        Without splitting string, hierarchy, model...
        with t as (
        select 'abcdefgh' str from dual
        )
        select  str,
        regexp_replace(initcap(regexp_replace(str,'(..)','\1 ')),'(..) ','\1') new_str
        from  t
        /
        
        STR      NEW_STR
        -------- ---------
        abcdefgh AbCdEfGh
        
        SQL> 
        SY.
        Great thinking...!

        Initially I thought SY is doing something wrong - coz I was "sure" that this cant be done using string functions...
        • 16. Re: Changing alternative letters into upper case
          jeneesh
          odie_63 wrote:
          BluShadow wrote:
          jeneesh wrote:
          MODEL
          is evil. ;)
          Evilness contest open ;)
          SQL> with t (str) as (
          2    select 'abcdefgh' from dual union all
          3    select 'ijklmnop' from dual
          4  )
          5  select t.str, x.result
          6  from t
          7     , xmltable(
          8         'string-join(
          9            for $i in 1 to string-length($s)
          10            let $c := substring($s,$i,1)
          11            return if ($i mod 2 = 0) then $c else upper-case($c),"")'
          12         passing str as "s"
          13         columns result varchar2(4000) path '.'
          14       ) x
          15  ;
          
          STR      RESULT
          -------- --------------------------------------------------------------------------------
          abcdefgh AbCdEfGh
          ijklmnop IjKlMnOp
          Lot and lot and lot and lot ... to learn....
          • 17. Re: Changing alternative letters into upper case
            887479
            Thanks a lot all...

            So many options....

            I would like to mark more answers as helpful, but I cannot :(
            • 18. Re: Changing alternative letters into upper case
              Solomon Yakobson
              BluShadow wrote:
              Neat SY.... ;)
              Well, there is one gotcha. Since we are adding a character for every two, extended string can possibly exceed 4000 bytes. So generic solution thould be:
              with t as (
                         select 'abcdefgh' str from dual
                        )
              select  str,
                      regexp_replace(initcap(regexp_replace(to_clob(str),'(..)','\1 ')),'(..) ','\1') new_str
                from  t
              /
              SY.
              • 19. Re: Changing alternative letters into upper case
                jeneesh
                Solomon Yakobson wrote:
                BluShadow wrote:
                Neat SY.... ;)
                Well, there is one gotcha. Since we are adding a character for every two, extended string can possibly exceed 4000 bytes. So generic solution thould be:
                with t as (
                select 'abcdefgh' str from dual
                )
                select  str,
                regexp_replace(initcap(regexp_replace(to_clob(str),'(..)','\1 ')),'(..) ','\1') new_str
                from  t
                /
                SY.
                I could not make it out- What difference the new code will make?

                Edited by: jeneesh on Nov 15, 2012 5:36 PM
                Ohooo got it...... I missed the TO_CLOB :(
                • 20. Re: Changing alternative letters into upper case
                  jeneesh
                  A bit value addition from me
                  with t as (
                             select 'abcdefgh' str from dual
                            )
                  select  str,
                          replace(initcap(regexp_replace(to_clob(str),'(..)','\1 ')),' ') new_str
                    from  t;
                  • 21. Re: Changing alternative letters into upper case
                    jeneesh
                    But both are not working,
                    with t as (
                               select lpad('abcdefgh',3000,'x')  str from dual
                              )
                    select  str,
                            regexp_replace(initcap(regexp_replace(to_clob(str),'(..)','\1 ')),'(..) ','\1') new_str
                      from  t;
                    
                    ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4500, maximum: 4000)
                    • 22. Re: Changing alternative letters into upper case
                      Solomon Yakobson
                      jeneesh wrote:
                      A bit value addition from me
                      And who said original string can't have spaces:
                      with t as (
                                 select 'abc def gh' str from dual
                                )
                      select  str,
                              regexp_replace(initcap(regexp_replace(to_clob(str),'(..)','\1 ')),'(..) ','\1') new_str,
                              replace(initcap(regexp_replace(to_clob(str),'(..)','\1 ')),' ') new_str1
                        from  t
                      /
                      
                      STR        NEW_STR         NEW_STR1
                      ---------- --------------- ---------------
                      abc def gh AbC DeF Gh      AbCDeFGh
                      
                      SQL> 
                      SY.
                      • 23. Re: Changing alternative letters into upper case
                        jeneesh
                        Solomon Yakobson wrote:
                        And who said original string can't have spaces:
                        Then everything will fail..
                        with t as (
                                   select ' abc def gh' str from dual
                                  )
                        select  str,
                                regexp_replace(initcap(regexp_replace(to_clob(str),'(..)','\1 ')),'(..) ','\1') new_str,
                                replace(initcap(regexp_replace(to_clob(str),'(..)','\1 ')),' ') new_str1
                          from  t;
                        
                        STR         NEW_STR         NEW_STR1      
                        ----------- --------------- ---------------
                         abc def gh  ABc DEf GH     ABcDEfGH     
                        • 24. Re: Changing alternative letters into upper case
                          Solomon Yakobson
                          jeneesh wrote:
                          But both are not working,
                          Interesting. I didn't realize INITCAP was never changed to take CLOB arguments. All it takes is CHAR/VARCHAR2/NVARCHAR2. So my solution is limited to 2666 bytes.

                          SY.
                          1 2 Previous Next