Replicate Function in SAP HANA Database using SQLScript
SQL Replicate function is used to repeat a given string value for a given number of times to build a new string output value. On SAP HANA database SQLScript replicate function is missing. On the other hand, SQL programmers can create user-defined functions to produce the same output as SQL Replicate function.
SQL Server had already provided a Transact-SQL Replicate function. In this SAP HANA database SQLScript tutorial I want to share source codes of two alternative SQL Replicate functions for HANA developers
HANA Database SQL Replicate Function using String Concatenate
Using a HANA database numbers table and using String_Agg function, SQL programmers can concatenate given string value for specified number of times. Following SQL user-defined function creates dynamically a numbers table using SAP HANA SERIES_GENERATE_INTEGER series function. And SQLScript String_Agg() string function builds the concatenation of given string into replicated output variable.
CREATE FUNCTION REPLICATE(
string nvarchar(10),
repeat_count int
)
RETURNS replicated nvarchar(4000)
LANGUAGE SQLSCRIPT AS
BEGIN
SELECT string_agg( :string , '') INTO "REPLICATED"
FROM SERIES_GENERATE_INTEGER( 1, 0, :repeat_count);
END;
Using above SQL code, you can create the Replicate function on your database. Then you can test the SQL Replicate string function to see how it works by executing below script.
do
begin
declare v_str varchar(3);
declare v_count int;
v_str := 'XYZ';
v_count := 4;
select
REPLICATE('A',5) replicated_1,
REPLICATE(:v_str,2) replicated_2,
REPLICATE(:v_str,:v_count) replicated_3
from dummy;
end;
SQL developers can see the output below for the SQL Select statement with Replicate SQL string function in the SELECT field list. I called Replicate function in 3 different forms.
SQL developers can refer to tutorial Create SQL Numbers Table for SAP HANA Database for use of series function SERIES_GENERATE_INTEGER. Also SQLScript programmers can have a look at STRING_AGG() SQL function for its use in a sample case of concatenating string values.
Create User-Defined SQL Replicate Function on HANA Database
And now let's our second and maybe the better performing user-defined function alternative for previous SQL string Replicate function.
Luckily, SAP HANA database developers can use built-in SQLScript string functions LPAD or RPAD to replicate a given string value for the specified number of times.
Please check following SQL codes which provide exactly the same output. So you can decide to use the Replicate UDF or to use string LPAD function and RPAD function for replication purposes.
declare v_str varchar(3);
declare v_count int;
v_str := 'XYZ';
v_count := 4;
select
REPLICATE('A',5) replicated_1,
REPLICATE(:v_str,2) replicated_2,
REPLICATE(:v_str,:v_count) replicated_3
from dummy;
select
LPAD('', 5, 'A') replicated_1,
LPAD('', 2 * LENGTH(:v_str), :v_str) replicated_2,
LPAD('', :v_count * LENGTH(:v_str), :v_str) replicated_3
from dummy;
select
RPAD('', 5, 'A') replicated_1,
RPAD('', 2 * LENGTH(:v_str), :v_str) replicated_2,
RPAD('', :v_count * LENGTH(:v_str), :v_str) replicated_3
from dummy;
So as a developer if you insist on creating a UDF for replicate function, following SQL function could be created and used to replicate string expressions in SQL
CREATE FUNCTION REPLICATE(
string nvarchar(10),
repeat_count int
)
RETURNS replicated nvarchar(4000)
LANGUAGE SQLSCRIPT AS
BEGIN
SELECT LPAD('', LENGTH(:string) * :repeat_count, :string) INTO "REPLICATED" FROM dummy;
END;
And above SQLScript Replicate UDF function can be used on a HANA database as follows
SELECT REPLICATE2('Hi!',3) from dummy;
