$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
0

HANA SQL Help with String functions

Jun 30, 2017 at 05:03 AM

116

avatar image
Former Member

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

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
Jun 30, 2017 at 06:21 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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.

0