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.

CodePudding user response：

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>

CodePudding user response：

Use the built-in TRANSLATE function to swap all characters in a string from lower-to-upper case and vice-versa:

UPDATE table_name
SET your_column = TRANSLATE(
                    your_column,
                    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
                    'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
                  );

If you only want to swap a specific character then split the string and only swap the characters on a particular substring.

For example, if you only want to swap the 3rd character:

UPDATE table_name
SET your_column = SUBSTR(your_column, 1, 2)       -- First 2 characters
                  || TRANSLATE(
                       SUBSTR(your_column, 3, 1), -- Third character
                       'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
                       'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
                     )
                  || SUBSTR(your_column, 4);      -- The remaining string

If you want a PL/SQL function then:

CREATE OR REPLACE FUNCTION swap_case(
  i_string IN VARCHAR2,
  i_start  IN PLS_INTEGER DEFAULT 1,
  i_length IN PLS_INTEGER DEFAULT NULL
) RETURN VARCHAR2 DETERMINISTIC
IS
  v_length PLS_INTEGER := COALESCE(i_length, LENGTH(i_string));
BEGIN
  RETURN SUBSTR(i_string, 1, i_start - 1)
      || TRANSLATE(
           SUBSTR(i_string, i_start, v_length),
           'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
           'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
         )
      || SUBSTR(i_string, i_start   v_length);
END;
/

Then:

SELECT swap_case('Hello World'),
       swap_case('Hello World', 3, 6)
FROM   DUAL;

Outputs:

fiddle