Skip to Content
avatar image
Former Member

Split string in CDS View

Hi experts,

I want to split a column from the select list and return the string till the first occurrence of space.

eg. 'SAMPLE DOC' is the value stored in BKPF-XBLNR and I want my view to return only 'SAMPLE' in this case.

Is this possible using string functions in ABAP CDS?

I tried the below code. But this does not work since the substring function expects a numeric literal as its third parameter.

Thanks,

Ajith Cheruvally

@AbapCatalog.sqlViewName: 'ZXXXXXX'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Test CDS for substring'
define view ZXXXXXX  as select from bkpf as posted{
    posted.mandt, 
    posted.bukrs, 
    posted.belnr, 
    posted.gjahr, 
    xblnr, 
    substring( xblnr , 1 , instr( posted.xblnr , ' ' ) ) as test
}


<br>
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Oct 21, 2016 at 12:05 PM

    hi Ajith,

    What error are you actually getting, is it within CDS editor in AiE? If you have access to instr, then I presume you are using a 750 environment?, unfortunately this is not available to me as I'm still in 740 (SP 13).

    If the editor is really throwing an error due to the numeric 3rd parameter as defined below,

    SUBSTRING(arg, pos, len)

    arg: see below

    pos and len: positive numeric literal not equal to zero

    The output from INSTR shows as INT4

    INSTR(arg, sub)arg: see below

    sub: non-empty numeric literal

    INT4

    Full reference here.

    https://help.sap.com/abapdocu_750/en/abencds_f1_sql_functions_character.htm

    You could try a cast around the instr function to convert it to numeric value like this. cast( 1 as abap.numc( 10 ) )

    This works for me in 740

    substring( xblnr , 1 , cast( 10 as abap.numc( 10 ) ) ) as test,

    Try the cast and see how it goes

    Sean

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 22, 2016 at 09:13 AM

    Hi Sean,

    Thanks for helping.

    Specifying length statically works like in the example you wrote.

    However, when I specify the length value dynamically, it does not accept it.

    I tried casting INT4 to NUMC( Though it is not possible to cast INT4 to NUMC) but got the same error.

    'Function SUBSTRING: at position 3, only Literals, possibly positive integers allowed'

    I even tried by adding length as a parameter of type numc to the view, but still the same error.

    Looks like it accepts only static numeric literal ... which does not help my situation.

    Do you have any other suggestions? Please share.

    Thanks,

    Ajith Cheruvally

    @AbapCatalog.sqlViewName: 'ZXXXXX'
    @AbapCatalog.compiler.compareFilter: true
    @AccessControl.authorizationCheck: #CHECK
    @EndUserText.label: 'Test CDS viewn'
    define view ZXXX_XX_XX  with parameters p_test : abap.numc(2) as select from bkpf as posted{
        posted.mandt, 
        posted.bukrs, 
        posted.belnr, 
        posted.gjahr, 
        xblnr, 
        substring(xblnr , 1, :p_test) as test
        }
    
    
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Ajith,

      Kindly tell me solution, how you split string at '_' into two variable.

      Regards,

      Ranjeet

  • Oct 24, 2016 at 02:10 PM

    hi Ajith, Apologies as I can't test this out in 750, but you may try some intermediatary / inner views to solve this.

    View 1: Creates a column with the result of the instr value in INT4, say instr_pos

    View 2: Casts the instr_pos to char, e.g. instr_char_val

    View 3: Casts the instr_char_val to numc, e.g. to instr_numc_val

    View4: Use of substring function, using instr_numc_val as the len parameter.

    Bit of a pain for sure, but as CDS functionality improves, you should be able to combine all of the 4 above in one Select statement.

    Let us know how it goes,

    Sean.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      It is not working, kindly suggest me how can we split any string at '_' into two variable.

      Regards,

      Ranjeet

  • avatar image
    Former Member
    Jun 22, 2017 at 07:58 AM

    Hi Experts,

    I am also facing same issue. Kindly suggest me how can we split any string at '_' into two variable.

    Regards,

    Ranjeet

    Add comment
    10|10000 characters needed characters exceeded