I know this question probably has an easy answer, but I can't get my head around it. I'm trying to, inside a loop, return a string (in the SQL output) with mixed capital and non-capital letters.

Example: If a name in the row is John Doe, the output will print JoHn DoE, or MiXeD CaPiTaL. This is my code (which I know is poor written but I need to use the cursor!):

declare

 aa_ VARCHAR2(2000);
 bb_ NUMBER:=0;
cc_ NUMBER:=0;

 CURSOR cur_ IS
 
 SELECT first_name namn, last_name efternamn FROM person_info
 ;
 
 begin
       
         FOR rec_ IN cur_ LOOP  
         
         
          dbms_output.put_line(rec_.namn);

       
       FOR bb_ IN 1.. LENGTH(rec_.namn) LOOP
          
          dbms_output.put(UPPER(SUBSTR(rec_.namn,bb_,1)));


     
  cc_ := MOD(bb_,2);

          
 IF cc_ = 0 THEN
 
dbms_output.put(UPPER(SUBSTR(rec_.namn,cc_,1))); 

 ELSE 
 
dbms_output.put(LOWER(SUBSTR(rec_.namn,2)));


END IF;
 
   
 end loop;
 
 dbms_output.new_line;
 
 
 end loop;
 
     end;

Again, I know the code is really bad but yeah, trying to learn!

Thanks in advance :)

CodePudding user response：

For learning purpose you can use code below (it is not efficient it is for learning of oracle features)

Steps :

1. split word on letters using connect by level
2. get Nth (level) occurence of one letter ('.?') from word using reg exp
3. convert to upper case every 2nd letter
4. concatenate back using list agg and sorting by letter number
5. used here function in with so you can apply it to any sql table

with
function mixed(iv_name varchar2) return varchar2 as
  l_result varchar2(4000);
begin
  with src_letters as
   (select REGEXP_SUBSTR(iv_name, '.?', level) as letter
          ,level lvl
      from dual
    connect by level <= length(iv_name)),
  mixed_letters as
   (select case
             when mod(lvl, 2) = 0 then
              letter
             else
              upper(letter)
           end as letter
          ,lvl
      from src_letters
     order by lvl)
  select listagg(letter) within group(order by lvl)
    into l_result
    from mixed_letters;
  return l_result;
end;

select mixed('text') from dual

CodePudding user response：

I'd put the text transformation into a function, rather than including all the logic in the body of the loop.

declare
    cursor c_people is
        select 'John' as first_name, 'Doe' as last_name from dual union all
        select 'Mixed', 'Capitals'
        from   dual;

    function mixCaps(inText varchar2) return varchar2
    is
        letter varchar2(1);
        outText varchar2(4000); 
    begin
        for i in 1..length(inText) loop
            letter := substr(inText,i,1);

            outText := outText ||
                case mod(i,2)
                    when 0 then lower(letter)
                    else upper(letter)
                end;
        end loop;

        return outText;
    end mixCaps;    

begin
    for person in c_people loop
        dbms_output.put_line(mixCaps(person.first_name|| ' ' || person.last_name));
    end loop;
end;

If performance was critical and you had large numbers of values, you might consider inlining the function using pragma inline (but then you wouldn't be using dbms_output anyway).