SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

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;
Code

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;
Code

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 Replicate function for ABAP developer on SAP HANA database

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;
Code

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;
Code

And above SQLScript Replicate UDF function can be used on a HANA database as follows

SELECT REPLICATE2('Hi!',3) from dummy;
Code

HANA database SQL user-defined function to replicate given string



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.