CODEWITHSUNDEEP
sqlhana
user10802510
user10802510

Reputation: 1

How to split column based on certain criteria

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

enter image description here

Upvotes: 0

Views: 962

Answers (1)

Joakim Danielson
Joakim Danielson

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

Related Questions