This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Nov 15, 2012 4:41 AM by Solomon Yakobson RSS

Changing alternative letters into upper case

887479 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    jeneesh wrote:
    MODEL
    is evil. ;)
  • 6. Re: Changing alternative letters into upper case
    jeneesh Guru
    Currently Being Moderated
    BluShadow wrote:
    jeneesh wrote:
    MODEL
    is evil. ;)
    Umm..

    I do remember that.. :)
  • 7. Re: Changing alternative letters into upper case
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

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