How do I update value of a VARCHAR variable on it's particular index in P-SQL?

Question

I am working on a program that swaps lowercase letters into uppercase and uppercase to lower in Oracle SQL. I want to update letter at index I but have no idea how to.

Answer 1

Here's one option (could be shortened by putting case expression directly into retval, but this is easier to follow (especially when another step - swap at index - gets involved.

SQL> create or replace function f_test(par_string in varchar2, par_index in number)
  2    return varchar2
  3  is
  4    letter varchar2(1);
  5    retval varchar2(20);
  6  begin
  7    for i in 1 .. length(par_string) loop
  8      letter := substr(par_string, i, 1);
  9      letter := case when lower(letter) = letter then upper(letter)
 10                     else lower(letter)
 11                end;
 12      retval := retval || letter;
 13    end loop;
 14    return retval;
 15  end;
 16  /

Function created.

SQL> select f_test('abCdE', 2) from dual;      --> expecting ABcDe

F_TEST('ABCDE',2)
--------------------------------------------------------------------------------
ABcDe

Including the index:

SQL> create or replace function f_test(par_string in varchar2, par_index in number)
  2    return varchar2
  3  is
  4    letter varchar2(1);
  5    retval varchar2(20);
  6  begin
  7    for i in 1 .. length(par_string) loop
  8      letter := substr(par_string, i, 1);
  9      letter := case when i = par_index then
 10                       case when lower(letter) = letter then upper(letter)
 11                            else lower(letter)
 12                       end
 13                else letter
 14                end;
 15      retval := retval || letter;
 16    end loop;
 17    return retval;
 18  end;
 19  /

Function created.

SQL> select f_test('abCdE', 2) from dual;      --> only 2nd letter should change case: b --> B 
                                               --> so expected result is aBCdE

F_TEST('ABCDE',2)
--------------------------------------------------------------------------------
aBCdE

SQL>

---

[EDIT]

If you want to replace character at certain position with something else, here's a simple example (improve it, if you want):

SQL> create or replace function f_char
  2    (par_string in varchar2, par_Index in number, par_char in varchar2)
  3  return varchar2
  4  is
  5    retval varchar2(20);
  6  begin
  7    retval := substr(par_string, 1, par_index - 1) || par_char ||
  8              substr(par_string, par_index + 1);
  9    return retval;
 10  end;
 11  /

Function created.

SQL> select f_char('abcde', 3, 'x') res1,
  2         f_char('abcde', 1, 'y') res2,
  3         f_char('abcde', 5, 'z') res3
  4  from dual;

RES1       RES2       RES3
---------- ---------- ----------
abxde      ybcde      abcdz

SQL>