Formatting a string in Oracle SQL

Question

I am trying to take a varchar2 that is the input of the procedure and format it then do some calculations with the new string. The input will be a series of comma-delimited names:

Example: name,id,address

What I need is to find a way to change the above to:

CAST(name AS VARCHAR2(200)) name, 
CAST(id AS VARCHAR2(200)) id, 
CAST(address AS VARCHAR2(200)) address

I tried looking at REGEXP_REPLACE but I am a beginner so I don't know how to properly use regex.

(this is for Oracle SQL by the way)

confused why your stored proc wouldn't just accept params for name, id, and address — Bryan Dellinger
– Bryan Dellinger, Commented Jul 13, 2020 at 19:16
I am supposed to have as input a list of column names which could 1 or more comma-delimited names. So I don't know how many names I'm getting at execution. Sorry if that wasn't clear in the question. — Daria Davaloo
– Daria Davaloo, Commented Jul 13, 2020 at 19:20
you will loop across the input string using PLSQL inside your procedure. looks for the delimter - in your case comma ',' and then substr the bits in between into a variable. — Randy
– Randy, Commented Jul 13, 2020 at 19:44
@DariaDavaloo 'how to format that string of names differently.' Could you show us please few examples what you want to get as a result? just few couples of pairs: input - required result — Sayan Malakshinov
– Sayan Malakshinov, Commented Jul 13, 2020 at 21:06

Sayan Malakshinov · Accepted Answer · 2020-07-13 21:01:48Z

Looks like this should work for you:

create or replace procedure p_test(params in varchar2) as 
   -- type for string array (collection/nested table)
   type t_str_array is table of varchar2(100);
   -- string array
   str_array t_str_array:=t_str_array();
   -- variable for a count of elements in comma-separated list
   cnt int;
   
   name    varchar2(100);
   id      varchar2(100);
   address varchar2(100);
begin
   -- count of elements in comma-separated list:
   cnt := regexp_count(params,'[^,]+');
   -- extending string collection up to CNT elements:
   str_array.extend(cnt);
   -- iterate and fill array:
   for i in 1..cnt loop
      str_array(i):=regexp_substr(params,'[^,]+',1,i);
   end loop;
   
   -- now we can set required variables to the values from array by their positions:
   name   :=str_array(1);
   id     :=str_array(2);
   address:=str_array(3);
   
   -- print them to check anddebug:
   dbms_output.put_line(utl_lms.format_message('name = %s, id = %s, addr = %s', name, id, address));
end;
/

And testing:

SQL> call p_test('Pups,1,Interstate 60');
name = Pups, id = 1, addr = Interstate 60

Call completed.

Littlefoot · Accepted Answer · 2020-07-13 19:20:34Z

0

You don't really need regular expressions; instr + substr do the job nicely as it is quite simple task.

SQL> create or replace procedure p_test (par_string in varchar2) is
  2    -- comma positions
  3    l_pos1    number := instr(par_string, ',', 1, 1);
  4    l_pos2    number := instr(par_string, ',', 1, 2);
  5    l_pos3    number := instr(par_string, ',', 1, 3);
  6    -- variables
  7    l_name    varchar2(200);
  8    l_id      varchar2(200);
  9    l_address varchar2(200);
 10  begin
 11    l_name    := substr(par_string, 1, l_pos1 - 1);
 12    l_id      := substr(par_string, l_pos1 + 1, l_pos2 - l_pos1 - 1);
 13    l_address := substr(par_string, l_pos2 + 1);
 14
 15    dbms_output.put_line('Name    = ' || l_name);
 16    dbms_output.put_line('ID      = ' || l_id);
 17    dbms_output.put_line('Address = ' || l_address);
 18  end;
 19  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec p_test('Little Foot,25,Manhattan 23 New York');
Name    = Little Foot
ID      = 25
Address = Manhattan 23 New York

PL/SQL procedure successfully completed.

SQL>

answered Jul 13, 2020 at 19:20

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

2 Comments

Daria Davaloo Over a year ago

Thanks for the quick answer Littlefoot. What I was looking for though was how to format that string of names differently. At the time of execution, I don't know how long this string is. My example had 3 names but it could vary

Littlefoot Over a year ago

You're welcome. INSTR searches for comma position, so - yes, it can vary. Did you try it? What problems did you meet? This can be improved, but - from what you posted as an example, it should be OK.

Prakhar Gupta · Accepted Answer · 2020-07-13 19:54:25Z

0

I am just writing the select statement having expressions made of substr and instr. You can use same expressions in your procedure as well to get the required output. I assumed example_string contains the comma seperated values of name,id,address.

SELECT SUBSTR(example_string,1,INSTR(example_String,',',1,1)-1) AS name, SUBSTR(example_string,INSTR(example_String,',',1,1)+1,INSTR(example_String,',',1,2)-INSTR(example_String,',',1,1)-1) AS id, SUBSTR(example_string,INSTR(example_String,',',1,2)+1) AS address FROM dual;

answered Jul 13, 2020 at 19:54

Prakhar Gupta

5035 silver badges12 bronze badges

Collectives™ on Stack Overflow

Formatting a string in Oracle SQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related