PL/SQL - String concatenation algorithm

Question

I'm working on a PL/SQL algorithm, with Oracle.

I currently have a procedure which have one single numeric parameter. My procedure have to create a string which contains as much '0' as the parameter value.

I am currently using a for loop to achieve this:

MY_STRING VARCHAR2(30);
FOR I IN 1..MY_PARAMETER 
LOOP
     MY_STRING := CONCAT(MY_STRING, '0');
END LOOP;

Is it possible to do it in a linear way ? I mean without a loop, or even with one single statement.

Any help would be appreciated !

Thanks.

Jeffrey Kemp · Accepted Answer · 2010-12-23 03:31:51Z

11

You can use LPAD() to achieve this:

SELECT LPAD('0', my_parameter, '0')
FROM DUAL

Here is the link to the manual:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions082.htm#i1371196

edited Dec 23, 2010 at 3:31

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

answered Dec 22, 2010 at 11:03

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

LaGrandMere Over a year ago

+1 : LPAd, that's it ! Nice answer. But maybe this query would better fit what is asked : SELECT LPAD('', my_parameter, '0') FROM DUAL because the parameter is only used to count the number of '0' ;)

Hal Over a year ago

That's exactly what I wanted ! Indeed, I just wanted to generate a string with a set number of '0', but I didn't knew about LPAD so thank you !

user330315 Over a year ago

@LaGrandMere: you are right. I didn't look closely enough at the example. I changed my code

Leigh Riffel Over a year ago

LPAD returns null if the first parameter is an empty string (null).

user330315 Over a year ago

@Leight: good catch :) I thought this was one of the situations where Oracle would treat the empty string as an empty string

Leigh Riffel · Accepted Answer · 2013-01-17 18:47:11Z

3

Demonstration of accepted answer using various input values.

set serveroutput on size 1000000 format wrapped

Declare
   my_parameter Number(3);
   my_string    Varchar2(10);

Begin
   DBMS_Output.Put_Line('Input Output');
   DBMS_Output.Put_Line('===== ======');

   For vLoopVar IN 0..5 Loop     
      my_parameter := vLoopVar;

      If (vLoopVar = 5) Then
         my_parameter := '';
      End If;

      DBMS_Output.Put(RPAD('~' || my_parameter || '~',6));

      --Method 1
      my_string := lpad('0',my_parameter,'0');
      DBMS_Output.Put_Line('~' || my_string || '~');

   End Loop;
End;
/

Output

Input Output
===== ======
~0~   ~~
~1~   ~0~
~2~   ~00~
~3~   ~000~
~4~   ~0000~
~~    ~~

edited Jan 17, 2013 at 18:47

answered Dec 22, 2010 at 15:16

Leigh Riffel

6,6813 gold badges38 silver badges48 bronze badges

2 Comments

Hal Over a year ago

You're completely right. I've tested it and lpad indeed returns null if the first parameter is an empty string. But now I concretely have to append the '0' string to another string so basically I do "LPAD(ORIGINAL_STRING, LENGTH(ORIGINAL_STRING)+MY_PARAMETER, '0')" which just do what I need.

Leigh Riffel Over a year ago

Assuming your ORIGINAL_STRING will never be NULL (or a NULL result is acceptable when it is), then your method will indeed work and be preferred

Collectives™ on Stack Overflow

PL/SQL - String concatenation algorithm

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related