CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
Manar Qassim
Manar Qassim

Reputation: 1

PL/SQL program to separate the code into its two parts

How to write a PL/SQL program to separate the code into its two parts as shown in the following example: if the input is ABC031, the output should be:

Product Name is: ABC
Serial Number is: 031

Hints: Use the following functions if needed:
instr( string1, string2 [, start_position [, nth_appearance ] ] )
substr( string, start_position, [ length ] )
length( string )
DBMS_OUTPUT.PUT_LINE( )

Upvotes: 0

Views: 76

Answers (1)

Littlefoot
Littlefoot

Reputation: 142798

Here are two examples, see if any of these helps.

SQL> set serveroutput on
SQL>
SQL> create or replace procedure p_test (par_input in varchar2)
  2  is
  3    l_product_name   varchar2(10);
  4    l_serial_number  varchar2(10);
  5  begin
  6    -- first example
  7    l_product_name  := substr(par_input, 1, 3);
  8    l_serial_number := substr(par_input, 4);
  9
 10    dbms_output.put_line('First example : ' || l_product_name ||', '|| l_serial_number);
 11
 12    -- second example, possibly better as it splits letters from digits
 13    l_product_name  := regexp_substr(par_input, '^[[:alpha:]]+');
 14    l_serial_number := regexp_substr(par_input, '[[:digit:]]+$');
 15
 16    dbms_output.put_line('Second example: ' || l_product_name ||', '|| l_serial_number);
 17  end;
 18  /

Procedure created.

SQL> begin
  2    p_test('ABC031');
  3  end;
  4  /
First example : ABC, 031
Second example: ABC, 031

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions