SQLScript Reverse String Function in SAP HANA
SAP HANA SQLScript Reverse function returns the reverse order of the input string function argument. SQLScript does not have a built-in reverse string function, because of this missing string function I created a simple SQL user-define function which can be used to reverse string values.
HANA Reverse string function forms the function output string by reading each character one by one starting from the end of the string.
Below SQL developers can find a SQLScript code block within in "Do Begin ... End" Anonymous Block.
First of all using SQL Length() string function, I get the total length of the input string variable.
Then assigning the string length into an integer parameter, I used this value in a WHILE Loop to execute the included code block for each character of the string.
In the While Loop reading each character one by one using SQL string function SubString()
Then I formed the output string which is the reversed string value, by concatenating the read character with Substring() function.
For concatenating each character to reversed string I preferred to use CONCAT() string function.
do begin
declare str nvarchar(5000) := 'SQLScript Reverse Function';
declare reversedstring nvarchar(5000) := '';
declare i int := length(str);
while :i > 0
do
reversedstring = concat(reversedstring, substring(str,i,1));
i := :i - 1;
end while;
select str as str, reversedstring as reversedstring from dummy;
end;
As developers can see in below screenshot, the input string 'SQLScript Reverse Function' is reversed by reading one by one from the end of the string towards to the first character into 'noitcnuF esreveR tpircSLQS'
I packed the above SQLScript code as follows and create SQL Reverse string function named REVERSE_STRING()
Programmers can create reverse string function on their SAP HANA databases using below Create Function DDL command.
CREATE FUNCTION REVERSE_STRING(inputString NVARCHAR(5000))
RETURNS reversedString NVARCHAR(5000)
LANGUAGE SQLSCRIPT AS
BEGIN
DECLARE i INT := length(:inputString);
reversedString := '';
WHILE :i > 0
DO
reversedString = CONCAT(:reversedString, SUBSTRING(:inputString,i,1));
i := :i - 1;
END WHILE;
END;
HANA SQLScript developers can use the SQL reverse function as follows:
do begin
declare str nvarchar(5000) := 'kodyaz.com';
select REVERSE_STRING(str) as "reverse" from Dummy;
end;
And the output of the reverse string function is seen in below screenshot.
