Prathamesh H
Prathamesh H

Reputation: 150

Split string in Hana to get the string elements in different rows without using user defined functions?

Is it possiible to split a comma separated string in Hana into rows without using a user defined function.

Something like this from oracle :

SELECT DISTINCT REGEXP_SUBSTR ('23,34,45,56','[^,]+',1,LEVEL) as "token"
FROM   DUAL
CONNECT BY REGEXP_SUBSTR ('23,34,45,56','[^,]+',1,LEVEL) IS NOT NULL
order by 1

Upvotes: 1

Views: 1877

Answers (2)

Mani Kandan
Mani Kandan

Reputation: 107

Yes we have STRING_SPLIT System defined function but it is will only applicable in MSSQLSERVER 2016 and later version.

Simply it supports from compatability level 130 and above.

So We need to write udf when we are working in previous version of sql server

Upvotes: 0

Lars Br.
Lars Br.

Reputation: 10396

The example you gave in the question mixes regular expression handling as well as hierarchy processing. Both can be done in SAP HANA with the appropriate commands.

SUBSTRING_REGEXPR is the HANA version of the function you used in the example.

As I'm not sure what you want to achieve with the CONNECT BY clause, I'll just vaguely hint towards the HIERARCHY-functions here.

Upvotes: 1

Related Questions