topic Split string in CDS View in Application Development and Automation Discussions

Split string in CDS View

ajith_c — Fri, 21 Oct 2016 05:45:03 GMT

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>

Re: Split string in CDS View

Former Member — Fri, 21 Oct 2016 12:05:02 GMT

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

Re: Split string in CDS View

ajith_c — Sat, 22 Oct 2016 09:13:29 GMT

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
    }

Re: Split string in CDS View

Former Member — Mon, 24 Oct 2016 14:10:39 GMT

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.

Re: Split string in CDS View

ranjeetabap — Thu, 22 Jun 2017 07:58:15 GMT

Hi Experts,

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

Regards,

Ranjeet

Re: Split string in CDS View

ranjeetabap — Thu, 22 Jun 2017 08:01:25 GMT

Hi,

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

Regards,

Ranjeet

Re: Split string in CDS View

ranjeetabap — Thu, 22 Jun 2017 08:06:00 GMT

Hi Ajith,

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

Regards,

Ranjeet

Re: Split string in CDS View

acapone01 — Fri, 21 May 2021 19:13:39 GMT

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?

Re: Split string in CDS View

radek_hosek2 — Tue, 12 Oct 2021 12:03:56 GMT

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-limitations-workarounds/

Radek

Re: Split string in CDS View

former_member334095 — Thu, 04 Nov 2021 12:10:59 GMT

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.

Re: Split string in CDS View

former_member334095 — Thu, 04 Nov 2021 12:13:58 GMT

Hello Ajith,

Did you find a solution to this probelm yet?

Regards,

Meby

Re: Split string in CDS View

ThorstenHoefer — Fri, 05 Nov 2021 06:38:46 GMT

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

Re: Split string in CDS View

rowieforms — Sun, 31 Jul 2022 13:15:38 GMT

Radek, you are godsend!

Re: Split string in CDS View

former_member743581 — Tue, 08 Nov 2022 20:32:43 GMT

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