How can I use 'LIKE' along with DELETE?

[Former Member]

Hello,

I am trying to remove specific data from an internal table. However, in order to remove, for example, everything that starts with 'sap' I would have to use the operator LIKE (WHERE str LIKE 'sap%').
The problem is that the SQL command DELETE doesn't allow me to do, for example:

          DELETE itab WHERE str LIKE 'sap%'

I get an error saying that LIKE isn't an allowed relational operator (such as EQ or GE).

Thank you very much in advance!

[Former Member]

Hi Rafael,

Instead if LIKE you could use a range table (like a select-option) and use the IN operator as shown below:

types:
  begin of lty_itab,
    str            type string,
    other_fields   type string,
  end of lty_itab.

data:
  li_itab          type standard table of lty_itab,
  lr_str           type range of string,
  lwa_str          like line of lr_str.

lwa_str-sign   = 'I'.
lwa_str-option = 'CP'.
lwa_str-low    = 'sap*'.
append lwa_str to lr_str.

delete li_itab where str in lr_str.

[Former Member]

  You could try to use something like

  DELETE itab WHERE str CA 'sap'. 

  CA is Contains Any

[tolga_polat]

Hi Former Member

Use range instead of LIKE,

Here is sample.

DATA : r_belnr TYPE RANGE OF bkpf-belnr.

DATA : BEGIN OF itab OCCURS 0,

             belnr TYPE bkpf-belnr,

            END OF itab.

DELETE itab WHERE belnr IN r_belnr.

For ranges http://help.sap.com/abapdocu_70/en/ABAPDATA_RANGES.htm

For 'SAP%'

sign   = 'I'.
option = 'CP'.
low    = 'SAP*'.
high   = space.

Regards

Message was edited by: Tolga POLAT

[Former Member]

Hi Rafael,

LIKE is not supported in DELETE statement. So you need to create range table for you requirement.

TYPES:

ty_condition TYPE RANGE OF string.

DATA:

lt_condition TYPE ty_condition,

ls_condition TYPE LINE OF ty_condition.

ls_condition-sign = 'I'.   "Indicates Including

ls_condition-option = 'CP'.  "Containing Pattern

ls_condition-low = 'SAP*'.  "Your condition string.

ls_condition-high = space.

APPEND ls_condition TO lt_condition.

DELETE itab WHERE <your_field_name> IN lt_condition.

[former_member205060]

DELETE ITAB  WHERE NAME CA 'SAP'.

Check by using F1 or http://help.sap.com/saphelp_45b/helpdata/en/34/8e72fb6df74873e10000009b38f9b8/content.htm

[Former Member]

hi rafael,

               like is not allowed in delete statement, to delete record from itab with some starting letters you can fellow code as satish did or u can go with below code .

DATA it_mara TYPE STANDARD TABLE OF mara INITIAL SIZE 0 WITH HEADER LINE.

SELECT * FROM  mara INTO TABLE it_mara UP TO 10 ROWS.

LOOP AT it_mara.

   IF it_mara-matnr+0(3) = 'SAP'.

     DELETE it_mara WHERE matnr = it_mara-matnr.

   ELSE.

     WRITE : it_mara-matnr.

   ENDIF.

ENDLOOP.

[Former Member]

Hi Rafael,

As suggested by Rod and Tolga, you should use range table.

Thanks,

Ankit.

[former_member189779]

Another simple way can be

LOOP at your table

Check if the value of first 3 char is SAP

if yes move 'X' to that field.

Modify table transporting that field.

ENDLOOP

delete itab where field eq 'X'.

[Former Member]

Hi Rafael,

Yes you cannot use like as a relational operator in where clause. Its show a syntax error.

If you want to use such pattern try with below code:

DATA:

   T_SPFLI TYPE STANDARD TABLE OF SPFLI,

   WA_SPFLI TYPE SPFLI.

SELECT *

   FROM SPFLI

   INTO TABLE T_SPFLI.

IF SY-SUBRC EQ 0.

   LOOP AT T_SPFLI INTO WA_SPFLI .

     IF WA_SPFLI-CARRID CP 'a*'.

       DELETE  T_SPFLI INDEX SY-TABIX.

     ENDIF.

   ENDLOOP.

ENDIF.

so in loop we check the condition as cp(contains pattern) which is same as like in where clause.

By looping check with contains pattern and delete the record based on matching.

Regards,

B.Vineesh

[Former Member]

Thanks a lot, everyone!
I used RANGE and everything worked

[raymond_giuseppi]

Just for information, expression "field CA string", is wrong here, it will select every records where the field contains at least one character from string,

DELETE itab WHERE str CA 'sap'

will delete records like 'Sure' 'aaaa' and 'pew'.

Simpliest check was

DELETE itab WHERE str CS 'sap'

Read log_exp - Comparison Operators for Character-Like Data Types.

Butr range will do the job and is easy to use with select-options parameters.

Regards,

Raymond