Method that uses a random value as parameter to swap the characters of a string

Question

I have this code that’s working perfectly, but I need random values to appear instead of the X. I want the returned result to be composed of random letters and the position of upper and lower case letters to be obeyed according to the user’s input into the function at the time of the tests. I use this function to "shuffle" people’s names. The first name is preserved and I want the rest of the name to appear with random characters.

Example:

• Entree:

Pedro Souza

• Upshot

Pedro Fscet

The number of letters must remain the same and must be case sensitive.

Does anyone know how I can do this? I’m using SQL DEVELOPER.

create or replace FUNCTION EMBARALHA_NOME (NOME IN VARCHAR2) RETURN VARCHAR2 AS
primeiro_Nome VARCHAR2(100);
embaralha VARCHAR2(100);
nome_Cortado VARCHAR2(100);
nome_Embaralhado VARCHAR2(100);
BEGIN
if (NOME is NULL) then
    nome_Embaralhado := NULL; 
  else 
    primeiro_Nome := NVL(SUBSTR(NOME, 0, INSTR(NOME, ' ')-1), NOME);
    nome_Cortado := LTRIM(NOME, primeiro_Nome);
    embaralha := REGEXP_REPLACE(nome_Cortado, '[A-Za-z]', 'x');
    nome_Embaralhado := CONCAT(primeiro_Nome, embaralha);
end if;
return nome_Embaralhado;
END EMBARALHA_NOME;

Answer 1

You can use Oracle’s DBMS_RANDOM package to generate random values. In addition to numbers, it also generates strings.

To generate a string with a quantity n use the following syntax: SELECT DBMS_RANDOM.STRING('L', n) FROM DUAL.

Substitute n by an integer number, i.e.:

SELECT DBMS_RANDOM.STRING('L', 8) FROM DUAL
-- vai resultar em algo como 'flawifka'

The 'L' in the Code refers to lowercase (lowercase characters only). You can substitute for:

• 'U' for uppercase characters only;
• 'A' for upper and lower case characters;
• 'X' for numbers and letters, but all letters will be uppercase (I don’t know why);
• 'P' for any character that can be printed.

Answer 2

create or replace FUNCTION EMBARALHA_NOME (NOME IN VARCHAR2) RETURN VARCHAR2 AS
primeiro_Nome VARCHAR2(100);
embaralha VARCHAR2(100);
nome_Cortado VARCHAR2(100);
nome_Embaralhado VARCHAR2(100);
param_r char(1);
BEGIN
if (NOME is NULL) then
    nome_Embaralhado := NULL; 
  else 
    primeiro_Nome := NVL(SUBSTR(NOME, 0, INSTR(NOME, ' ')-1), NOME);
    nome_Cortado := LTRIM(NOME, primeiro_Nome);
    for i in 1..length(nome_Cortado)
    loop
      if substr(nome_Cortado,i,1) <> ' ' then
        --https://docs.oracle.com/database/121/TTPLP/d_random.htm#TTPLP71246
        if substr(nome_Cortado,i,1) = upper(substr(nome_Cortado,i,1)) then
          param_r := 'U';
        else
          param_r := 'l';
        end if;
        nome_Embaralhado := nome_Embaralhado || dbms_random.string(param_r,1);
      else
        nome_Embaralhado := nome_Embaralhado || ' ';
      end if;
    end loop;
end if;
--return nome_Embaralhado;
nome_Embaralhado := primeiro_Nome || nome_Embaralhado;
END EMBARALHA_NOME;