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

Question

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

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

I have this code that is working perfectly, but I need random values to appear in place of X. I want the returned result to be composed of random letters and that the position of uppercase and lowercase letters be obeyed according to the user input in 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 in random characters.

Example:

Input:

Pedro Souza

Result

Pedro Fscet

The amount of letters should remain the same and should be case sensitive.

Does anyone know how I can do this? I am 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;

sql oracle replace

asked by anonymous 20.10.2017 / 17:36

2 answers

1

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

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

Replace n with an integer, i.e .:

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

The 'L' in the code refers to lowercase (only lowercase characters). You can replace with:

'U' to only uppercase characters;
'A' for uppercase and lowercase characters;
'X' for numbers and letters, but all letters will be uppercase (I do not know why);
'P' for any and every character that can be printed.

20.10.2017 / 17:44

How to bulkcopy an excel file into an sql table Select values that are not in another table

score 1 · Accepted Answer

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;