1 2 Previous Next 24 Replies Latest reply: Nov 15, 2012 6:41 AM by Solomon Yakobson RSS

    Changing alternative letters into upper case

    887479
      Hi All,

      11gR2 DB.

      Requirement is to Change alternative letters into upper case starting from the first letter.
      (this is some kind of token in our application)

      How can we achieve that?

      Input - abcdefgh
      output - AbCdEfGh

      Thanks in advance
        • 1. Re: Changing alternative letters into upper case
          BluShadow
          One method suitable for 10g upwards...
          SQL> ed
          Wrote file afiedt.buf
          
            1  with t as (select 'abcdefgh' as txt from dual)
            2  --
            3  select replace(sys_connect_by_path(decode(mod(l,2),1,upper(ch),ch),','),',') as new_txt
            4  from (
            5        select level l, substr(txt,level,1) as ch
            6        from   t
            7        connect by level <= length(txt)
            8       )
            9  where connect_by_isleaf = 1
           10  connect by l = prior l+1
           11* start with l = 1
          SQL> /
          
          NEW_TXT
          ---------------------------------------------------------------------------------------------
          AbCdEfGh
          
          SQL>
          In 11g you could use the new LISTAGG function instead of sys_connect_by_path method
          • 2. Re: Changing alternative letters into upper case
            Frank Kulash
            Hi,

            Write a PL/SQL function that loops through the input string, builiding an output string one character at a time.

            You cn do it in pure SQL, but it won't be pretty. See {message:id=10095021} for ways to split the string into 1-character substrings.
            • 3. Re: Changing alternative letters into upper case
              BluShadow
              Listagg method..
              SQL> ed
              Wrote file afiedt.buf
              
                1  with t as (select 'abcdefgh' as txt from dual)
                2  --
                3  select listagg(decode(mod(l,2),1,upper(ch),ch)) within group (order by l) as new_txt
                4  from (
                5        select level l, substr(txt,level,1) as ch
                6        from   t
                7        connect by level <= length(txt)
                8*      )
              SQL> /
              
              NEW_TXT
              -----------------------------------------------------------------------------------------
              AbCdEfGh
              • 4. Re: Changing alternative letters into upper case
                jeneesh
                MODEL:
                with t as
                (
                  select 'abcdefgh' str
                  from dual
                )
                select *
                from t
                  model
                   dimension by (1 rn)
                   measures(str,cast(null as varchar2(999)) str_out)
                   rules 
                    iterate(999) until(iteration_number+1 = (length(str[1])/2)+1)
                    (
                     str_out[1] = str_out[1]||initcap(substr(str[1],((iteration_number+1)*2)-1,2))
                    );
                Between, where are you going to use this? If it is inside a PL/SQL program, it is better to use a function to so it, if you are trying to work on some table data, you could do it in SQL.

                Edited by: jeneesh on Nov 15, 2012 5:06 PM
                • 5. Re: Changing alternative letters into upper case
                  BluShadow
                  jeneesh wrote:
                  MODEL
                  is evil. ;)
                  • 6. Re: Changing alternative letters into upper case
                    jeneesh
                    BluShadow wrote:
                    jeneesh wrote:
                    MODEL
                    is evil. ;)
                    Umm..

                    I do remember that.. :)
                    • 7. Re: Changing alternative letters into upper case
                      BluShadow
                      And another alternative doing 2 characters at a time with initcap...
                      SQL> ed
                      Wrote file afiedt.buf
                      
                        1  with t as (select 'abcdefgh' as txt from dual)
                        2  --
                        3  select listagg(initcap(ch)) within group (order by l) as new_txt
                        4  from (
                        5        select level l, substr(txt,(level*2)-1,2) as ch
                        6        from   t
                        7        connect by level <= (length(txt)+1)/2
                        8*      )
                      SQL> /
                      
                      NEW_TXT
                      ---------------------------------------------------------------------
                      AbCdEfGh
                      • 8. Re: Changing alternative letters into upper case
                        887479
                        The data is stored in a table, and the data volume is low - below 10k.

                        Which will be the best method out of these, normally?
                        • 9. Re: Changing alternative letters into upper case
                          jeneesh
                          884476 wrote:
                          The data is stored in a table, and the data volume is low - below 10k.
                          You will have to change the MODEL a bit:
                          with t as
                          (
                            select 'abcdefgh' str from dual union all
                            select 'xyzxyzxyz' str from dual 
                          )
                          select str_in,str_out
                          from t
                            model
                             partition by (str)
                             dimension by (1 rn)
                             measures(str as str_in,cast(null as varchar2(999)) str_out)
                             rules 
                              iterate(999) until(iteration_number+1 = (length(str_in[1])/2)+1)
                              (
                               str_out[1] = str_out[1]||initcap(substr(str_in[1],((iteration_number+1)*2)-1,2))
                              );
                          • 10. Re: Changing alternative letters into upper case
                            Solomon Yakobson
                            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.
                            • 11. Re: Changing alternative letters into upper case
                              odie_63
                              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
                               
                              • 12. Re: Changing alternative letters into upper case
                                BluShadow
                                884476 wrote:
                                The data is stored in a table, and the data volume is low - below 10k.

                                Which will be the best method out of these, normally?
                                For multiple rows on your table something like this...
                                SQL> ed
                                Wrote file afiedt.buf
                                
                                  1  with t as (select 'abcdefgh' as txt from dual union all
                                  2             select 'fred' from dual union all
                                  3             select 'yet another string' from dual)
                                  4  --
                                  5  -- end of simulated table
                                  6  --
                                  7  select listagg(initcap(ch)) within group (order by l) as new_txt
                                  8  from (
                                  9        select txt, level l, substr(txt,(level*2)-1,2) as ch
                                 10        from   t
                                 11        connect by substr(txt,(level*2)-1,2) is not null
                                 12        and     txt = prior txt
                                 13        and     prior sys_guid() is not null
                                 14       )
                                 15* group by txt
                                SQL> /
                                
                                NEW_TXT
                                ----------------------------------------------------------------------
                                AbCdEfGh
                                FrEd
                                YeT AnOtHeR StRiNg
                                just replace "t" in the query with your table name and "txt" with your column name etc. as required
                                • 13. Re: Changing alternative letters into upper case
                                  BluShadow
                                  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.
                                  Neat SY.... ;)
                                  • 14. Re: Changing alternative letters into upper case
                                    BluShadow
                                    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
                                    Nah, model is more evil I'm afraid. At least that one I can understand. :D
                                    1 2 Previous Next