Split string into multiple rows using SQL in SAP HANA

[KonradZaleski]

Introduction

In this post I would like to describe options for splitting concatenated string into multiple rows using SQL in SAP HANA. Depending on which version of HANA system you are working you can choose the one which is valid.

Creating custom function (when SAP HANA system version is 2.0 SP02 or lower)

If your HANA system is of version 2.0 SP2 or older, then there is no in-built function for splitting strings, that's why you will need to create custom function.

Scenario 1.

You have the comma separated string 'A1,A2,A3,A4,A5,A6' which you want to display as multiple rows. Below there is a code for table function splitting comma separated string into multiple lines. 

FUNCTION "_SYS_BIC"."TMP::TF_SPLIT_STRING" ( INPUT_STRING VARCHAR(5000) ) 

	RETURNS TABLE

	(

		"OUTPUT_SPLIT" VARCHAR(5000)

	)

	LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER AS

BEGIN



	DECLARE COUNTER INT := 1;

	SPLIT_VALUES = SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY;	

	SELECT SUBSTR_AFTER(:INPUT_STRING,',') || ',' INTO INPUT_STRING FROM DUMMY;

	

	WHILE( LENGTH(:INPUT_STRING) > 0 )	

	DO

	

	   SPLIT_VALUES =	

	   

	   				SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY				   

					   UNION 	   

					SELECT SINGLE_VAL FROM :SPLIT_VALUES;

	

	   SELECT SUBSTR_AFTER(:INPUT_STRING,',') INTO INPUT_STRING FROM DUMMY;

	   	

	END WHILE;

	

	RETURN

	

	SELECT SINGLE_VAL AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES; 



END

*If your string is separated by different delimiter simply replace ',' with other symbol.

Now let's query the function for the string which we need to split:

DO

BEGIN



	DECLARE TEST_STRING VARCHAR(50) := 'A,B,C,D,E,F,G,H';

	

	SELECT "OUTPUT_SPLIT" FROM "_SYS_BIC"."TMP::TF_SPLIT_STRING" (:TEST_STRING); 



END

Result:

Using SQLSCRIPT built-in Library (recommended when SAP HANA version is 2.0 SP03 or higher)

Version 2.0 SP03 of SAP HANA offers new enhancement of SQLScript library - SQLSCRIPT_STRING. Functions within this library enables easy way of splitting string into multiple rows. Additionally developer has more flexibility to define splitting rules.

Scenario 1

You have the comma separated string

'A1,A2,A3,A4,A5,A6'

which you want to display as multiple rows.

Use following code:

I. Explicitly declare usage of SQLSCRIPT_STRING library. Assign alias for the library (in this example name of the alias is "LIBRARY", but you can assign any other name)

II. Explicitly declare output table

III. Use SPLIT_TO_TABLE function. Combine it with the alias assigned in point I. As input for SPLIT_TO_TABLE function use string which you want to split, and after comma define delimiter (in this example comma is delimiter). At the end you need to assign output of that function to the table variable defined in point II.

IV. Query the table variable

After running following the query string will be splitted into multiple rows:

DO

BEGIN



	USING SQLSCRIPT_STRING AS LIBRARY;

	

	DECLARE TEST_OUTPUT TABLE(RESULT NVARCHAR(5000));

	

	DECLARE TEST_STRING VARCHAR(50) := 'A,B,C,D,E,F,G,H';



	TEST_OUTPUT = LIBRARY:SPLIT_TO_TABLE(:TEST_STRING,',');

	

	SELECT * FROM :TEST_OUTPUT;



END

Result:

Scenario 2

There is a string which combines first and last name, phone and the address:

'Sarah Blake, 98 921 29 30, 270 Sycamore Street Brookfield, WI 53045, US' 

You want to split this string into three values: full name, phone, address.

Use following code:

DO

BEGIN



	USING SQLSCRIPT_STRING AS LIBRARY;

	

	DECLARE TEST_OUTPUT TABLE(RESULT NVARCHAR(5000));

	

	DECLARE TEST_STRING VARCHAR(100) := 'Sarah Blake, 98 921 29 30, 270 Sycamore Street Brookfield, WI 53045, US';



	TEST_OUTPUT = LIBRARY:SPLIT_TO_TABLE(:TEST_STRING,',',2);

	

	SELECT * FROM :TEST_OUTPUT;



END

Result:

Here I used third optional parameter (MAXSPLIT) which is available for SPLIT_TO_TABLE and assigned value of 2. This way I will limit the split to only first two comma occurrences (subsequent commas will not be considered).

Besides defining delimiter for split, you can also specify maximum number of splits. Function takes first n-number of delimiter occurences, and remaining part is being displayed in the last row. As you can see in the string from the example there are 4 commas, so if this parameter would not be specified, then in output there would be 5 rows.

Scenario 3

There is a string which combines number, date and time. It's separated by #, DATE, TIME string:

'10000000123#20190101DATE115737TIME'

You want to split this string into three strings: number, date, time.

Use following code:

DO

BEGIN



	USING SQLSCRIPT_STRING AS LIBRARY;

	

	DECLARE TEST_OUTPUT TABLE(RESULT NVARCHAR(5000));

	

	DECLARE TEST_STRING VARCHAR(50) := '10000000123#20190101DATE115737TIME';



	TEST_OUTPUT = LIBRARY:SPLIT_REGEXPR_TO_TABLE(:TEST_STRING, '\#|[A-Z]*[A-Z]') ;

	

	SELECT * FROM :TEST_OUTPUT WHERE "RESULT" != '';



END

Output:

Here I used function SPLIT_REGEXPR_TO_TABLE. It can be combined with regular expressions, which gives user even more options for defining logic for splitting strings. Regular expression from the example splits string after each occurencs of # symbol or uppercase alphabet string.

Summary

SAP HANA SPS 02 introduced built-in libraries giving SQL developers new functions. In SPS 03 new librarary SQLSCRIPT_STRING has been added which contains multiple functions for manipulating with strings. If you want to find out more about SQLSCRIPT_STRING library, check SQLScript reference by SAP.

If your system is running on older version of SAP HANA, as workaround you can develop custom function as described in the post.

In my next post you can check how to Split table column value into multiple rows using SQL in SAP HANA

Thanks for reading!

[beecher92099]

Good explanation how to split a string.  My situation though is I have a table with a column, whose values are a comma separated list.  How to leverage this SPLIT function on a table column and generate a separate row per split value?

 

Header_1 | Header_2

A | 1,2,3

B | 4,5

 

Convert to

Header_1 | Header_2

A | 1

A | 2

A | 3

B | 4

B | 5

[former_member259926]

Hi Konrad

 

First I would like to thank you for sharing the possibilities of "SQLSCRIPT_STRING" library .

 

I tried to use it for one of my use cases , but I came across insufficient privileges error . I tried to find the authorizations required for Using libraries in procedure  , but could not find documentation related to that . Could you help me with authorizations required to use libraries in procedures.

Thanks

[KonradZaleski]

What is your SAP HANA system version? Is it 2.0 SP03 or higher ?

[former_member259926]

HANA System version is 2.0 SP03

[KonradZaleski]

Do you have "PUBLIC" role assigned to your user? I think this should be sufficient to run the library functions (they persist in SYS schema).

[former_member607044]

Good explanation!!

[KonradZaleski]

Hi Beecher,

Sorry for late reply. I've just published a new post which describes your scenario (link added at the end of this post).

[former_member206016]

Hi Konrad,

Firstly, thanks a lot for showing us how to use SQLSCRIPT_STRING, it was a very helpful document.

I have a requirement where I have an input string as shown below, where data is been consumed with column and its respective data using Tilda and Pipe delimiters. Can you please guide how split the data using the new functionality.

Input String:

~COLUMN_1~2F-8S~COLUMN_2~SEC-WATER~COLUMN_3~2SF-S-S10~COLUMN_4~10~COLUMN_5~20.44~COLUMN_6~7358.40~COLUMN_7~TRUE~|

~COLUMN_1~2F-8S~COLUMN_2~SEC- WATER ~COLUMN_3~2SF-S-M-10~COLUMN_4~10~COLUMN_5~55.91~COLUMN_6~20127.60~COLUMN_7~ TRUE ~|

~COLUMN_1~2F-8S~COLUMN_2~SEC- WATER ~COLUMN_3~2SF-S-P-10~COLUMN_4~10~COLUMN_5~10.84~COLUMN_6~3902.40~COLUMN_7~ TRUE ~|

 

Need to show the data as below

COLUMN_1	COLUMN_2	COLUMN_3	COLUMN_4	COLUMN_5	COLUMN_6	COLUMN_7
2F-8S	SEC- WATER	2SF-S-S10	10	20.44	7,358.40	TRUE
2F-8S	SEC- WATER	2SF-S-M-10	10	55.91	20,127.60	TRUE
2F-8S	SEC- WATER	2SF-S-P-10	10	10.84	3,902.40	TRUE

 

Note: I am using 2.0 SPS 3

[KonradZaleski]

Hi Santhosh,

You can use SPLIT_REGEXPR function. Here is an example (it includes first two strings which you provided):

DO

BEGIN



	USING SQLSCRIPT_STRING AS LIB;

	

	DECLARE COL1 VARCHAR(150);

	DECLARE COL2 VARCHAR(150);

	DECLARE COL3 VARCHAR(150);

	DECLARE COL4 VARCHAR(150);

	DECLARE COL5 VARCHAR(150);

	DECLARE COL6 VARCHAR(150);

	DECLARE COL7 VARCHAR(150);

	DECLARE COL8 VARCHAR(150);

	DECLARE COL9 VARCHAR(150);

	DECLARE COL10 VARCHAR(150);

	DECLARE COL11 VARCHAR(150);

	DECLARE COL12 VARCHAR(150);

	DECLARE COL13 VARCHAR(150);

	DECLARE COL14 VARCHAR(150);

	DECLARE COL15 VARCHAR(150);

	DECLARE COL16 VARCHAR(150);

	

	DECLARE STRING VARCHAR(200) := '~COLUMN_1~2F-8S~COLUMN_2~SEC-WATER~COLUMN_3~2SF-S-S10~COLUMN_4~10~COLUMN_5~20.44~COLUMN_6~7358.40~COLUMN_7~TRUE~|';

	

	DECLARE STRING2 VARCHAR(200) := '~COLUMN_1~2F-8S~COLUMN_2~SEC-WATER~COLUMN_3~2SF-S-M-10~COLUMN_4~10~COLUMN_5~55.91~COLUMN_6~20127.60~COLUMN_7~TRUE~|';

	

	(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16) :=  LIB:SPLIT_REGEXPR(:STRING,'\~',15);

		

	RES1 = SELECT COL3 AS COLUMN_1,COL5 AS COLUMN_2,COL7 AS COLUMN_3,COL9 AS COLUMN_4,COL11 AS COLUMN_5,COL13 COLUMN_6, COL15 AS COLUMN_7 FROM DUMMY;



	(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16) :=  LIB:SPLIT_REGEXPR(:STRING2,'\~',15);



	RES2 = SELECT COL3 AS COLUMN_1,COL5 AS COLUMN_2,COL7 AS COLUMN_3,COL9 AS COLUMN_4,COL11 AS COLUMN_5,COL13 COLUMN_6, COL15 AS COLUMN_7 FROM DUMMY;





	SELECT * FROM :RES1

	UNION

	SELECT * FROM :RES2;

	

END

In future if you have specific question, raise it through SAP community rather than in comments 🙂

[raja396p]

Very useful and detailed instructions

[former_member669357]

Please consider that your TF from Scenario 1 doesnt work it gets only one value without split-string.

Therefore I had to adjust it:

 

FUNCTION "TFU_SPLIT_STRING" (INPUT_STRING VARCHAR(5000), SEPARATOR VARCHAR(1)) 

	RETURNS TABLE

	(

	 "OUTPUT_SPLIT" VARCHAR(5000)

	)

	LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER AS

BEGIN

DECLARE COUNTER INT := 1;

DECLARE LOC INT := 0;



	--Check if Separator is in :INPUT_STRING 	

	SELECT LOCATE(:INPUT_STRING, :SEPARATOR) INTO LOC FROM DUMMY;

	IF LOC=0 THEN -- if not, use only :INPUT_STRING as Return Value

		SPLIT_VALUES = SELECT :INPUT_STRING SINGLE_VAL FROM DUMMY;

		RETURN SELECT SINGLE_VAL AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES; 

	ELSE

		SPLIT_VALUES = SELECT SUBSTR_BEFORE(:INPUT_STRING,:SEPARATOR) SINGLE_VAL FROM DUMMY;

		SELECT SUBSTR_AFTER(:INPUT_STRING,:SEPARATOR) || :SEPARATOR INTO INPUT_STRING FROM DUMMY;

	END IF;

	

	

	WHILE( LENGTH(:INPUT_STRING) > 0 )	

	DO

	

	   SPLIT_VALUES =	

	   

	   				SELECT SUBSTR_BEFORE(:INPUT_STRING,:SEPARATOR) SINGLE_VAL FROM DUMMY				   

					   UNION 	   

					SELECT SINGLE_VAL FROM :SPLIT_VALUES;

	

	   SELECT SUBSTR_AFTER(:INPUT_STRING,:SEPARATOR) INTO INPUT_STRING FROM DUMMY;

	   	

	END WHILE;

	



	

	

	

	RETURN

	

	SELECT SINGLE_VAL AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES; 



END;

[former_member337435]

Hi,

I am trying to pass multiple values from a Calculation View  for each Input parameter to a Table Function. This table function internally calls a Calculation view which has these multiple input parameters mapped. Since Table function input parameters cannot take multiple values, as per the post above, since I am in hana 2 sp03, I thought of using SQLSCRIPT_STRING  inside the table function to split the input parameter values.

When I add the below statement after the BEGIN in the Table function and when I activate it, I get the following error.

USING SQLSCRIPT_STRING AS LIBRARY;

 

Repository: Encountered an error in repository runtime extension;Model inconsistency. Using library is not supported in current SQLScript configuration

 Can I not use this inside a Table function?

When I give it in the SQL console, it works.

DO

BEGIN

USING SQLSCRIPT_STRING AS LIBRARY;

DECLARE TEST_OUTPUT TABLE(RESULT NVARCHAR(5000));

DECLARE TEST_STRING VARCHAR(50) := 'N100,N102';

TEST_OUTPUT = LIBRARY:SPLIT_TO_TABLE(:TEST_STRING,',');

SELECT * FROM :TEST_OUTPUT;

END

 

 

 

 

[mcnarik]

Hi Konrad,

I like the way you explained the function, however the solution itself is unfortunately not good because the performance is very poor.

I did some tests and below you can see comparison of your solution (SPLIT_STRING_01) with another solution. It shows, that the solution SPLIT_STRING_03 is twice faster:

/*

----------------------------------------------------------

 Generate data

----------------------------------------------------------

*/

DROP FUNCTION "PMYNARIK"."SPLIT_TEST_DATA_GEN";

CREATE FUNCTION "PMYNARIK"."SPLIT_TEST_DATA_GEN" (in_records integer) 

	RETURNS table ( "STRING" nclob )

	LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER AS

BEGIN



	declare lv_int integer;

	declare lv_int_max integer;

	declare lv_str nclob;

	declare lt_output table ( "STRING" nclob );

	

	

	-- Generate data

	lv_str = '';

	lv_int_max = :in_records - 1;

	for lv_int in 1 .. :lv_int_max do

		lv_str = :lv_str || :lv_int || ',';

	end for;



	lv_str = :lv_str || (:lv_int + 1);	

	lt_output."STRING"[1] = :lv_str;

	

	return

		select "STRING" from :lt_output;

END;









/*

----------------------------------------------------------

 SPLIT_STRING_01



 12.546 seconds

----------------------------------------------------------

*/

DROP FUNCTION "PMYNARIK"."SPLIT_STRING_01";

CREATE FUNCTION "PMYNARIK"."SPLIT_STRING_01" ( in_records integer ) 

	RETURNS TABLE

	(

		"OUTPUT_SPLIT" VARCHAR(5000)

	)

	LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER AS

BEGIN



	DECLARE COUNTER INT := 1;

	declare INPUT_STRING nclob;

	

	

	-- Get data

	select "STRING" into INPUT_STRING from "PMYNARIK"."SPLIT_TEST_DATA_GEN"(:in_records);

	

	

	

	SPLIT_VALUES = SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY;	

	SELECT SUBSTR_AFTER(:INPUT_STRING,',') || ',' INTO INPUT_STRING FROM DUMMY;

	

	WHILE( LENGTH(:INPUT_STRING) > 0 )	

	DO

	

	   SPLIT_VALUES =	

	   

	   				SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY				   

					   UNION 	   

					SELECT SINGLE_VAL FROM :SPLIT_VALUES;

	

	   SELECT SUBSTR_AFTER(:INPUT_STRING,',') INTO INPUT_STRING FROM DUMMY;

	   	

	END WHILE;

	

	RETURN

	

	SELECT SINGLE_VAL AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES; 

	

END;





SELECT "OUTPUT_SPLIT" FROM "PMYNARIK"."SPLIT_STRING_01" (5000);









/*

----------------------------------------------------------

 SPLIT_STRING_03



 6.646 seconds

----------------------------------------------------------

*/

CREATE FUNCTION "PMYNARIK"."SPLIT_STRING_03" ( in_records integer ) 

	RETURNS TABLE

	(

		"OUTPUT_SPLIT" VARCHAR(5000)

	)

	LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER AS

BEGIN



	declare INPUT_STRING nclob;

	

	declare lv_str nclob;

	declare lv_value nclob;

	declare lv_index int;

	declare lv_separator VARCHAR(2) default ',';

	declare lt_output_split table (

		"OUTPUT_SPLIT" VARCHAR(5000)

		);

	

	

	-- Get data

	select "STRING" into INPUT_STRING from "PMYNARIK"."SPLIT_TEST_DATA_GEN"(:in_records);

	lv_str = :INPUT_STRING;





	-- Parse to virtual table

	lt_output_split = select '' as "OUTPUT_SPLIT" from dummy;

	lv_index = 0;

	WHILE( LENGTH(:lv_str) > 0 )	

	DO

		if LOCATE(:lv_str, :lv_separator) > 0 then

			lv_value = SUBSTR_BEFORE (:lv_str, :lv_separator);

			lv_index = :lv_index + 1;

			lt_output_split."OUTPUT_SPLIT"[:lv_index] = :lv_value;

			lv_str = SUBSTR_after (:lv_str, lv_separator);

			

		else

			lv_value = :lv_str;

			lv_index = :lv_index + 1;

			lt_output_split."OUTPUT_SPLIT"[:lv_index] = :lv_value;

			lv_str = '';

		end if;

	END WHILE;

	

	

	return 

		select * from :lt_output_split;

END;





SELECT "OUTPUT_SPLIT" FROM "PMYNARIK"."SPLIT_STRING_03" (5000);

 

Kind Regards,

Pavel Mynarik

[KonradZaleski]

Hi Pavel,

Thanks for that analysis!

Anyway I think that Library split function is definitely the best.

Regards,

Konrad

[former_member432910]

Hi Lalitha, did you resolve this issue?

[uli_m]

Hi Konrad,

I try to do a "Where-used" on BW4 and hence want to read Scripted HANA Views from "_SYS_REPO"."ACTIVE_OBJECT" column CDATA.

As this is a LOB column it may contain (and often does) > 5000 Characters.

When using SQLSCRIPT_STRING, I do get an error.

Is there a good way to split the LOB string independent of length?

DO BEGIN USING SQLSCRIPT_STRING AS LIBRARY;	

declare clob_data NVARCHAR(5000);

DECLARE SQL_CODE TABLE(Result NVARCHAR(5000));

select substring( cdata, locate(cdata, 'Begin Procedure Script')-11, 

                       ( locate(cdata, '/</definition>') - locate(cdata, 'Begin Procedure Script') ) ) 

       into clob_data 

       from "_SYS_REPO"."ACTIVE_OBJECT" where OBJECT_NAME = 'CV_NAME';

SQL_CODE = LIBRARY:SPLIT_REGEXPR_TO_TABLE(CLOB_DATA, ', ') ;	

	SELECT * FROM :SQL_CODE;

END

 

Thanks and regards,

Uli

[lucdealethea]

Hello,

I attached the usage of built-in SQLSCRIPT_STRING library in AMDP.

Off course the table function created here is useless, but allows to show case the invocation in AMDP.

SELECT RESULT AS MEMBER FROM SQLSCRIPT_STRING:SPLIT_TO_TABLE(String, ',', -1)

Data Preview on CDS Table Function



CDS Table Function

@EndUserText.label: 'Split Comma Separated String max 1333'

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientDependent: false

define table function ZTF_SPLIT_STRG

with parameters 

p_nodename : RSDM4NODENAME

returns {

  base_member : UJ_DIM_MEMBER;  

  }

  implemented by method zcl_amdp_split_strg=>strg_to_table;

CLASS zcl_amdp_split_strg DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .



PUBLIC SECTION.

INTERFACES if_amdp_marker_hdb .

TYPES:

BEGIN OF g_t_mbr,

  base_member TYPE UJ_DIM_MEMBER,

END OF g_t_mbr.

CLASS-METHODS:

strg_to_table

FOR TABLE FUNCTION ZTF_SPLIT_STRG.

ENDCLASS.



CLASS zcl_amdp_split_strg IMPLEMENTATION.

method strg_to_table

  BY DATABASE FUNCTION FOR HDB

  LANGUAGE SQLSCRIPT

  OPTIONS READ-ONLY

.

RETURN

SELECT RESULT AS BASE_MEMBER FROM SQLSCRIPT_STRING:SPLIT_TO_TABLE(:p_nodename, ',', -1)

order by 1

;

ENDMETHOD.

ENDCLASS.

[harsh-9825]

Use below select query to split string by comma and transformed into table format. This select query improve your performance as well.

lv_string_with_comma = 'Class,Tuition,College,University,Company';
lt_split_string_into_table = select

                               numberstable.Element_Number,

                               SUBSTR_REGEXPR( '([,])([^,]*)(?=,)' IN CONCAT(CONCAT(',',lv_scntyp_with_without_range),',') OCCURRENCE NumbersTable.Element_Number GROUP 2) Temp_string

                                from "PUBLIC".DUMMY as SplitString,

                                "PUBLIC".SERIES_GENERATE_INTEGER(1, 0, OCCURRENCES_REGEXPR(',' IN lv_scntyp_with_without_range) + 1 ) as NumbersTable WITH HINT(NO_INLINE);

[samsekhar50]

Thanks For a Nice Blog.