Split string in CDS View

[ajith_c]

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>

[former_member184795]

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

[ajith_c]

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
    }

[ranjeetabap]

Hi Ajith,

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

Regards,

Ranjeet

[former_member184795]

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.

[ranjeetabap]

Hi,

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

Regards,

Ranjeet

[ranjeetabap]

Hi Experts,

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

Regards,

Ranjeet

[acapone01]

Did anybody ever find a solution for this? It seems I cannot use an INSTR function or a variable in the 2nd or 3rd position of the Substring function. This seems like a serious limitation for CDS development. How are we supposed to split up a string if the exact position within the string varies record by record?

This is an example of the type of substring syntax throwing the error:

SUBSTRING(rate,1,instr(rate,'%'))

How do we determine the above instr(rate,'%') value and then use it within the substring function?

[radek_hosek2]

Try:

String Functions in ABAP CDS Views – Known Limitations and Workarounds, BY DINESH KUMAR REDDY, AUG 9, 2018.

Link: https://visualbi.com/blogs/sap/sap-bw-hana/sap-hana/string-functions-abap-cds-views-known-limitation...

Radek

[rowieforms]

Radek, you are godsend!

[former_member334095]

Is there a solution to this yet?

substring( TextObj , 1, instr(TextObj,' ') ) is still giving error "Function SUBSTRING: at position 3, only Literals, possibly positive integers allowed"

Does CDS provide a workaround for this? It should be possible to find the substring till a dynamic position based on a particular character using a single statement.

[former_member334095]

Hello Ajith,

Did you find a solution to this probelm yet?

Regards,

Meby

[ThorstenHoefer]

Currently only fix values are possible in ABAP SQL string functions.

There is a workaround by using HANA Views or Calculation Views.

Please have a look at https://visualbi.com/blogs/sap/sap-bw-hana/sap-hana/extended-view-based-hana-views/

With Version 7.55 SQL expressions are allowed as parameter (length).
This should solve the problem.

https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/abensql_string_func.htm

Regards

Thorsten

[former_member743581]

Hi,

I found a solution that is not so "pretty", but many of you explained here that it is not possible to use dynamic values in the "STRING", "LEFT" and "RIGHT" functions, but I need the solution and that’s what I did , I hope it helps those who need the same result.

I created a new CDS with only the table key, the complete value and the value that I need to do the split before the "_":

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'TESTE'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
define view entity ZI_TESTE_ALYSSON
  as select from ztest_te_alysson
{
  field1,

  case
    when instr(field1, '_') = 2
      then LEFT( field1, 1 )

    when instr(field1, '_') = 3
      then LEFT( field1, 2 )

    when instr(field1, '_') = 4
      then LEFT( field1, 3 )

    when instr(field1, '_') = 5
      then LEFT( field1, 4 )

    when instr(field1, '_') = 6
      then LEFT( field1, 5 )

    when instr(field1, '_') = 7
      then LEFT( field1, 6 )

    when instr(field1, '_') = 8
      then LEFT( field1, 7 )

    when instr(field1, '_') = 9
      then LEFT( field1, 8 )

    when instr(field1, '_') = 10
      then LEFT( field1, 9 )

    when instr(field1, '_') = 11
      then LEFT( field1, 10 )

     else field1
      end as split
}<br>

Result:

If you want the dynamic value after the "_" or "/" or "-", you can change it to the command:

when instr(field1, '/' ) = 2
then RIGHT( field1, 1 )
...