on 06-30-2017 6:03 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.