cancel
Showing results for 
Search instead for 
Did you mean: 

HANA SQL Help with String functions

Former Member
0 Kudos

Experts,

SQL novice here. I need some help with an urgent requirement on how to extract a string value in a calc view. I have an existing field in the view that is in this format.

"B.765973.01.01"

"B.76512345"

"B.98723.01.02.03"

Essentially, there will be multiple periods in the string. It could be one period or more than one period. But at least one. First period is always in the second position where as second period can be in anyplace. I need to extract the string that is before the second period into a new calculated field. So for above data, this is what I need to extract

"B.765973.01.01" --> "B.765973"

"B.76512345" --> "B.76512345"

"B.98723.01.02.03" --> "B.98723"

Thanks in advance

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

There's currently (HANA 2 SP01) no option to do that efficiently in a graphical calc view.

In plain SQL it's straight forward by using regular expression matching:

with data as (select 'B.765973.01.01' as dat from dummy
            union all select 'B.76512345' as dat from dummy
        union all select 'B.98723.01.02.03' as dat from dummy)
select 
    dat
    , substring_regexpr ('B.\d*' in "DAT") as result
from data;

So, the easiest way would probably be to have table function that does this conversion for you and join it with your base data table/projection.

Former Member
0 Kudos

Thanks Lars. I was able to achieve this in the graphical view, although not very elegant. I used a combination of leftstr, strlen, rightstr, instr.

Answers (0)