SQL repeat scalar function

The idea for this post came from Jonathan Heinz who brought to my attention the REPEAT SQL scalar function in a comment to a post. I have to admit I had not heard of it so this post is examples of how I played with it to become familiar with its use.

Being a scalar function it can be used within a Select, Set, or Values statement. Its syntax is:

REPEAT('characters-to-repeat', number-of-times-to-repeat)

I started playing with it in ACS's Run SQL Scripts, using a Values statement:

VALUES REPEAT('test', 3)

In the above I want to repeat the word 'test' three times. When executed the result is:

00001 ------------ testtesttest

If character or numeric data is used the result is variable length character format, VARCHAR.

I can do the same with a number:

VALUES REPEAT (123, 5)

Here I am repeating the number 123 file times.

00001 --------------- 123123123123123

Even though the result looks like a number, it is VARCHAR.

How could I use this in a RPG program?

01 **free 02 ctl-opt option(*srcstmt) ; 03 dcl-s String char(31) ; 04 dcl-s Number packed(15 : 5) ; 05 exec sql SET :String = REPEAT('< test >', 3) ; 06 dsply ('1. ' + String) ; 07 exec sql SET :String = REPEAT('< test >', 100) ; 08 dsply ('2. ' + String) ; 09 exec sql SET :String = REPEAT(123, 3) ; 10 dsply ('3. ' + String) ; 11 Number = %dec(String : 15 : 5) ; 12 dsply ('4. ' + %char(Number)) ;

Line 1: If it is written in 2024 it has to be modern RPG.

Line 2: I add this control option to all my programs. It makes problem diagnosis so much simpler.

Lines 3 and 4: Definition of the variables I will be using in this program.

Line 3: I am using the SQL Set statement to move the result of the Repeat into the variable called String. I am repeating the string '< test >' three times.

Line 4: I display the results:

DSPLY 1. < test >< test >< test >

Which shows the string was repeated three times.

Line 7: What happens if I repeat more times than it will fit in the RPG variable? Here I am repeating 100 times.

Line 8: The result looks like:

DSPLY 2. < test >< test >< test >< test

The repeat pattern does not fit in the variable String. I only get 3.75 of the repeat pattern.

Line 9: Here I am repeating the number 123 three times.

Line 10: Remember the result is not numeric, it is VARCHAR:

DSPLY 3. 123123123

Line 11: I am using the Convert to Decimal built in function, %DEC, to convert the VARCHAR to decimal in the packed variable Number.

Line 12: Now the result is a number:

DSPLY 4. 123123123.00000

In the next example program I want to insert the default value into all the columns of a SQL DDL Table. First I need a table:

01 CREATE TABLE QTEMP.TESTTABLE ( 02 ID_COLUMN SMALLINT GENERATED ALWAYS AS IDENTITY, 03 COLUMN1A VARCHAR(10) DEFAULT 'COL 1', 04 COLUMN1B VARCHAR(10), 05 COLUMN2A DECIMAL(3,0) DEFAULT -1, 06 COLUMN2B DECIMAL(3,0), 07 COLUMN3A DATE DEFAULT '01/01/1900', 08 COLUMN3B DATE, 09 COLUMN4A TIMESTAMP DEFAULT '1900-09-24-12.17.34.123456', 10 COLUMN4B TIMESTAMP)

The default for the columns where I have not given a default value will be null.

01 **free 02 ctl-opt option(*nodebugio : *srcstmt) ; 03 dcl-s SQL varchar(200) ; 04 dcl-s ColumnValues varchar(100) ; 05 exec sql SET :ColumnValues = REPEAT('DEFAULT,', 9) ; 06 %subst(ColumnValues : %len(ColumnValues) : 1) = '' ; 07 SQL = 'INSERT INTO QTEMP.TESTTABLE VALUES(' + 08 ColumnValues + ')' ; 09 exec sql EXECUTE IMMEDIATE :SQL ;

Line 5: I use the Set SQL statement and the Repeat scalar function to create a repeat for nine times for the word default followed by a comma. I repeat for nine times as the Table has nine columns.

> EVAL ColumnValues COLUMNVALUES = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFA' 61 'ULT,DEFAULT, '

The string ends with a comma. I need to remove that.

Line 6: I replace the last character in the variable using a Substring built in function, BiF. I am using the Length BiF, %LEN, to get the length of the string within the variable, and replace the last character with null.

> EVAL ColumnValues COLUMNVALUES = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFA' 61 'ULT,DEFAULT '

Lines 7 and 8: I can build my SQL Insert statement, and use the ColumnValues variable for the values of the columns:

> EVAL SQL SQL = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'INSERT INTO QTEMP.TESTTABLE VALUES(DEFAULT,DEFAULT,DEFAULT,D' 61 'EFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT ) ' 121 ' ' 181 ' '

Line 9: I now use the SQL EXECUTE IMMEDIATE statement to execute the Insert statement in the variable SQL.

The results of the Insert look like:

ID_COLUMN --------- 1 COLUMN1A COLUMN1B --------- -------- COL 1 <NULL> COLUMN2A COLUMN2B -------- -------- -1 <NULL> COLUMN3A COLUMN3B ---------- -------- 1900-01-01 <NULL> COLUMN4A COLUMN4B -------------------------- -------- 1900-09-24-12.17.34.123456 <NULL>

And finally I am going to show Jonathan's SQL statement with the Replace, it was to obscure an user's email address:

SELECT EMAIL_ADDRESS, SUBSTR( EMAIL_ADDRESS, 1, 1) || repeat('x', LOCATE_IN_STRING( EMAIL_ADDRESS, '@') -2) || SUBSTR( EMAIL_ADDRESS, LOCATE_IN_STRING(EMAIL_ADDRESS, '@')) FROM TESTFILE;

Which gives me:

EMAIL_ADDRESS 00002 ---------------- ---------------- SIMON@RPGPGM.COM Sxxxx@RPGPGM.COM

Thank you Jonathan for the feedback, and the opportunity to play with something new to me.

 

You can learn more about SQL's Replace scalar function from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.