How to convert each character of string into rows in sap hana

[naga_92]

Hello All,

Is there any function in hana which we can use to convert string into each rows of data.

input : ABCD

output :

A

B

C

D

Thanks,

Nagendra

[Abhishek_Hazra · Accepted solution]

Hi Nagendra,

You can define your own function as below, this way you can split a string without a delimiter (as in your case)

create procedure "split_string"
( 
in input_text nvarchar(5000) 
)
language sqlscript reads sql data as
begin
  declare storages nvarchar(5000) array;
  declare positions integer;
  positions := 1;

  while :positions <= length(:input_text) do
  storages[:positions] := substr(:input_text,:positions,1);
  positions := :positions + 1;
  end while;

  temp = unnest(:storages) as ("split");
  select * from :temp;
end; 

This will split your input text string into individual characters. For example you can try the below segment in sql console.

do
begin
  declare storages nvarchar(5000) array;
  declare input_text nvarchar(5000);
  declare positions integer;
  input_text := 'ABCDE';
  positions := 1;

  while :positions <= length(:input_text) do
  storages[:positions] := substr(:input_text,:positions,1);
  positions := :positions + 1;
  end while;

  temp = unnest(:storages) as ("split");
  select * from :temp;
end;

Hope this helps 🙂

Best Regards,
Abhi

[Cocquerel]

You may also use one of the split functions from the Built-in Library SQLSCRIPT_STRING : see https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.05/en-US/0fd739184bc343dfae61908919...

[KonradZaleski]

Hi Nagendra,

Please check my blogpost about splitting column value into multiple rows with SQL.

Hope it will help.