Reputation: 231
I am using HANA 1.0 SPS12. Is there a way to split delimited string in SAP HANA.
I have Pipe delimited string as -
v_input = '1111|2222|3333|4444'
I need output base on position of delimited part -
select seperate_string(v_input,pos) from dummy;
for e.g.
select seperate_string('1111|2222|3333|4444',1 ) from dummy; --1111
select seperate_string('1111|2222|3333|4444',2 ) from dummy; --2222
select seperate_string('1111|2222|3333|4444',3 ) from dummy; --3333
Is it possible to do this with SUBSTR_REGEXP?
I tried below coded but it did not work -
select SUBSTR_REGEXPR('[^\|]+' IN '1111|2222|3333|4444' GROUP 1 ) from dummy;
Upvotes: 1
Views: 19239
Reputation: 36
Yes, You can try the following SUBSTRING_REGEXPR()
SELECT SUBSTRING_REGEXPR('[^|]+' IN '1111|2222|3333|44|555' FROM 1 OCCURRENCE 4) from dummy;
--44
SELECT SUBSTRING_REGEXPR('[^|]+' IN '1111|2222|3333|44|555' FROM 1 OCCURRENCE 2) from dummy;
--2222
Upvotes: 2
Reputation: 10396
Yes, that's possible with SUBSTR_REGEXPR()
.
select SUBSTR_REGEXPR('(.+)\|(.+)\|(.+)\|(.+)' IN '1111|2222|3333|4444' GROUP 1 ) from dummy;
-- 1111
select SUBSTR_REGEXPR('(.+)\|(.+)\|(.+)\|(.+)' IN '1111|2222|3333|4444' GROUP 2 ) from dummy;
-- 2222
select SUBSTR_REGEXPR('(.+)\|(.+)\|(.+)\|(.+)' IN '1111|2222|3333|4444' GROUP 3 ) from dummy;
-- 3333
select SUBSTR_REGEXPR('(.+)\|(.+)\|(.+)\|(.+)' IN '1111|2222|3333|4444' GROUP 4 ) from dummy;
-- 4444
The regex builds four match-groups over the characters before a |
and at the end of the string. The GROUP x
parameter in the SUBSTR_REGEXPR()
function specifies, which match-group should be returned.
Upvotes: 1