Reputation: 1
I want to split a column using HANA Database view SQL as seen below:
Essentially, a column has a value of 1000, 100 or 100 in a string after 4 values
For example:
ABCD1000XYZ
ABCD100XYZ
ABCD10XYZ
I need to split the column into
ABCD 1000 XYZ
ABCD 100 XYZ
ABCD 10 XYZ
Upvotes: 0
Views: 962
Reputation: 52118
The regular expression to use for identifying the different groups looks like
(^.{4})(1[0]{1,})([A-Z0-9 ]*)(D.*$)
where each group is within ()
To extract each group in a select you need to use this reg expression 4 times (or 3, see below) and select one group per time (column)
SUBSTRING_REGEXPR('(^.{4})(1[0]{1,})([A-Z0-9 ]*)(D.*$)' IN text_name GROUP 1)
So the Sql query would be
SELECT SUBSTRING_REGEXPR('(^.{4})(1[0]{1,})([A-Z0-9 ]*)(D.*$)' IN text_name GROUP 1) AS Col1,
SUBSTRING_REGEXPR('(^.{4})(1[0]{1,})([A-Z0-9 ]*)(D.*$)' IN text_name GROUP 2) AS Col2,
SUBSTRING_REGEXPR('(^.{4})(1[0]{1,})([A-Z0-9 ]*)(D.*$)' IN text_name GROUP 3) AS Col3,
SUBSTRING_REGEXPR('(^.{4})(1[0]{1,})([A-Z0-9 ]*)(D.*$)' IN text_name GROUP 4) AS Col4
FROM some_table
Since the first column is always the 4 for characters it could be extracted with a normal SUBSTRING call
SELECT SUBSTRING(text, 1, 4) AS Col1, ...
I hope this helps, I don't have access to SAP Hana so the Sql is untested although the regular expression has been tested. Edit, I noted that you have a space in the fourth row so I added it to the reg expression.
Upvotes: 1