$(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: true, vro: true, 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
avatar image
Former Member

HANA SQL Help with String functions


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.




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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jun 30, 2017 at 06:21 AM

    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)
        , 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.

    Add comment
    10|10000 characters needed 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.