CODEWITHSUNDEEP
sqlregexhana
Anirudh D
Anirudh D

Reputation: 231

Split delimited string in SAP HANA

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: 19238

Answers (2)

RenitaR
RenitaR

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

Lars Br.
Lars Br.

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

Related Questions