How to do STUFF function syntax in SAP HANA Queries

[former_member641884]

SELECT STUFF('SQL Tutorial', 1, 3, 'HTML');

I need to know the syntax and function used for similar like stuff function in sql

Provide with the examples.

[KonradZaleski · Accepted solution]

There is no STUFF function in HANA, however you can combine SUBTRING functions to get similair behovior. Try something like below (you can create custom scalar function or use SUBSTRING directly in your query:

DO
BEGIN


	DECLARE "START" INTEGER = 1;
	DECLARE "LENG" INT := 3;
	DECLARE "STRING" VARCHAR(100) := 'SQL Tutorial';
	DECLARE "STUFF_STRING" VARCHAR(100) := 'HTML';
	
	SELECT SUBSTRING(:STRING,1,1 - :START) ||  :STUFF_STRING || SUBSTRING(:STRING, :START + :LENG, LENGTH(:STRING)) FROM DUMMY;


END

For your example it would be:

select 
	substring('SQL Tutorial',1, 1-1 ) || 'HTML' || substring('SQL Tutorial',1+3, length('SQL Tutorial'))  as STUFF_STRING
from 
	dummy;