更新时间:2023-11-22 14:12:28
,无需执行任何过程,就可以使用以下SQL(限制:它假定csv字段中的值的最大数量).我只会将其用于特殊目的.
without a procedure you can use the following SQL (restriction: it assumes a maximum number of values in your csv-field). I would use it only for adhoc purposes.
CREATE COLUMN TABLE "TEST_SPLIT"(
"SOME_KEY" VARCHAR(10),
"CSV_STR" VARCHAR(1000) );
INSERT INTO "TEST_SPLIT" ("SOME_KEY", "CSV_STR") VALUES ('1', 'hello world');
INSERT INTO "TEST_SPLIT" ("SOME_KEY", "CSV_STR") VALUES ('2', 'one,two,three');
INSERT INTO "TEST_SPLIT" ("SOME_KEY", "CSV_STR") VALUES ('3', NULL);
Select * from
( select "SOME_KEY" "KEY", "ELEMENT_NUMBER" "ORD",
SUBSTR_REGEXPR('(?<=^|,)([^,]*)(?=,|$)' IN "TEST_SPLIT"."CSV_STR" OCCURRENCE "SERIES"."ELEMENT_NUMBER" GROUP 1) "VAL"
from "TEST_SPLIT",
SERIES_GENERATE_INTEGER(1, 1, 10 ) "SERIES" -- replace 10 with your max. number of values in CSV-Field
)
where "VAL" is not null
order by "KEY", "ORD"
[1]: HANA:拆分字符串?