While reading this page, I stumbled upon a trick (by Tom Kyte) to generate an alphanumeric character string of any length using the Oracle SQL function RPAD. The emphasis here is on the length of the string, not its content. Let’s say you want to generate a 10 character string, you can easily do it like this:

scott@EDDEV> select
  2    rpad('&&first_string',&&total_length, '&&pad_with_string')
  3  from dual;
Enter value for first_string: a
Enter value for total_length: 10
Enter value for pad_with_string: z
old   2:   rpad('&&first_string',&&total_length, '&&pad_with_string')
new   2:   rpad('a',10, 'z')

RPAD('A',1
----------
azzzzzzzzz

To generate a 15 character length string:

scott@EDDEV> define total_length=15
scott@EDDEV> /
old   2:   rpad('&&first_string',&&total_length, '&&pad_with_string')
new   2:   rpad('a',15, 'z')

RPAD('A',15,'Z'
---------------
azzzzzzzzzzzzzz

You get the picture. first_string and pad_with_string can be any alphanumeric character(s), total_length can be any integer. If the length of the first_string is greater than the total_length, the function will be equivalent to SUBSTR. For example:

scott@EDDEV> define first_string=hello world
scott@EDDEV> define total_length=4
scott@EDDEV> /
old   2:   rpad('&&first_string',&&total_length, '&&pad_with_string')
new   2:   rpad('hello',4, 'z')

RPAD
----
hell

scott@EDDEV> select substr('hello world',1,4) from dual;

SUBS
----
hell

Note that LPAD works the same way as RPAD, but instead of padding to the right, it pads to the left:

scott@EDDEV> define first_string=a
scott@EDDEV> define total_length=10
scott@EDDEV> define pad_with_string=z
scott@EDDEV> select
  2    lpad('&&first_string',&&total_length, '&&pad_with_string')
  3  from dual;
old   2:   lpad('&&first_string',&&total_length, '&&pad_with_string')
new   2:   lpad('a',10, 'z')

LPAD('A',1
----------
zzzzzzzzza

This usage of RPAD and LPAD comes in handy when you want to test your programs with character input values having different lengths.

---

Possibly related:

• More on Generating Strings
• Triggers and Constraints
• Random string generator
• Strong Encryption in ColdFusion MX 7
• The Easy Way to Generate an RSS Feed from Your Forum Posts

---

Tagged dbms_random, rpad | Post a Comment