SAP HANA列值拆分为多行

title: 场景
ODS.MESSAGE的NAME字段是由多个字符拼接而成，间隔符为"、"

参考：[blogs.sap.com](https://blogs.sap.com/2019/05/14/split-table-column-value-into-multiple-rows-using-sql-in-sap-hana/)

NAME
a、b、c
d、e

DO
BEGIN
	DECLARE MAX_OCCURRENCES_COUNT INT;

	SELECT
		MAX(OCCURRENCES_REGEXPR('、' IN NAME) + 1)
	INTO
		MAX_OCCURRENCES_COUNT
	FROM
		ODS.MESSAGE;

	SELECT DISTINCT
		SUBSTR_REGEXPR('[^、]+' IN NAME OCCURRENCE SERIES."ELEMENT_NUMBER") AS NAME
	FROM
		ODS.MESSAGE
		CROSS JOIN SERIES_GENERATE_INTEGER(1,0, :MAX_OCCURRENCES_COUNT) SERIES
	WHERE
		SUBSTR_REGEXPR('[^、]+' IN NAME OCCURRENCE SERIES."ELEMENT_NUMBER") IS NOT NULL;

END

NAME
a
b
c
d
e