CODEWITHSUNDEEP
sqloracle-databaseplsql
Vikash Kumar
Vikash Kumar

Reputation: 51

Oracle function to replace all names to their nick names?

I have a table called nicknames

Names   nickname
vikram  vik
James   Jim
Robert  Bob
Charles Dick
Richard Dick
Rich    Dick

Want to create an Oracle function to replace names with their nicknames from the table in an input string

func_nicknames('vikram, James, Rajesh, Robert') should return the value 'vik, Jim, Rajesh, Bob'

CREATE OR REPLACE FUNCTION func_nicknames( in_val varchar2)
RETURN VARCHAR2
IS
O_VAL VARCHAR2(100) := in_val;
BEGIN
SELECT REPLACE(O_VAL,t.name,t.nickname) INTO O_VAL FROM nicknames t;
RETURN(O_VAL);
END func_nicknames;

The above code is throwing an error.

In SQL Server, the below code works fine:

CREATE OR ALTER FUNCTION getNicknames(@val NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) AS
BEGIN
    DECLARE @result NVARCHAR(MAX);
    SET @result = @val;
    SELECT @result = REPLACE(@result, name, nickname)
    FROM nicknames;
    RETURN @result;
END;

Similar code I want to create in Oracle. Working code for oracle:

CREATE OR REPLACE FUNCTION getNicknames(in_val VARCHAR) RETURN VARCHAR IS
ret VARCHAR(2000);
v VARCHAR(2000);
CURSOR cur IS SELECT SRC_VAL_STR, TGT_VAL_STR FROM nicknames;
BEGIN
    ret := in_val;
    FOR x IN cur
    LOOP
    SELECT REPLACE(ret, x.name, x.nickname) INTO v FROM DUAL;
    ret := v;
    END LOOP;
    RETURN ret;
END;

Upvotes: 1

Views: 160

Answers (2)

Monika Lewandowska
Monika Lewandowska

Reputation: 486

Your SQL returns more than one row. It is an error. Try this:

create or replace function f_nicks (pNames in varchar2) 
 return varchar2
 is
     tab dbms_utility.uncl_array;  
     nCntElements number := 0;  
     vEl varchar2(32000);  
     o_nicks varchar2(1000);
 BEGIN  
    dbms_utility.comma_to_table(pNames, nCntElements, tab);  
   
     select  listagg (nvl( n.nickname, t.listname), ',') nicks into o_nicks
        from (select trim(column_value) listname, rownum lp 
             from table(tab) ) t , nicknames n   where n.name (+) =  t.listname  ;
 

  return o_nicks;
 END;

--- Test function

select f_nicks ('James, vikram, James,James, Rajesh, Robert') from dual;

db fiddle

Upvotes: 2

0xdb
0xdb

Reputation: 3697

First you have to convert the parameter to a table, the rest is very straightforward:

create or replace function getNicknames (names varchar2) return varchar2 is
    ret varchar2 (32000);
begin
    with names (name) as (
        select trim (column_value)
        from xmlTable (('"'||replace (names, ',', '","')||'"')) x
    )
    select 
        listagg (coalesce (nickname, na.name), ', ') within group (order by null) into ret 
    from names na
    left join nicknames ni on ni.name=na.name;
    return ret;
end;
/

Execution and outcome:

exec dbms_output.put_line ('result='||getNicknames ('vikram, James, Rajesh, Robert'));    

result=vik, Jim, Rajesh, Bob

On db<>fiddle.

Upvotes: 3

Related Questions