Right-left CP comparison in SELECT?

Question

I have a table has a column "A" which has values contains "*". I want to search a word which like column A.

Table1 Example;

index ColumnA
1     New*
2     *york
3     Cat*
4     *ew*
.
.
.

@search = "Newyork"

I want to create a select statement search "Newyork" in column A. So the search will return to me index 1,2 and 4.

CP is useless because I want to search opposite direction.

data: lf_search(10) value 'Newyork'.
select * from Table1 where ColumnA CP lf_search.
endselect.

Is there any way to select table has pattern value in its column?

Many thanks.

Answer 1

There is no direct way to do this. I would approach this way:

• first to get all the table content
• then compare one by one and delete

SELECT ColumnA " Avoid SELECT *
  FROM table1
  INTO TABLE @DATA(lt_table1). " Add a where clausule if possible to avoid getting all the table
LOOP AT lt_table1 INTO DATA(wa_table1).
  IF lf_search NP wa_table1-ColumnA. " NP = NOT (... CP ...)
    DELETE lt_table1 INDEX sy-tabix.
  ENDIF.
ENDLOOP.

If you really need to make this process thru database. I think there is a possibility, very unusual. First create a table by copying the table1 (let's call it table1_bis). Then...

* Delete entire table1_bis db tab
DELETE FROM table1_bis.
* Insert the search item in the table
DATA(wa_table1_bis) = VALUE table1_bis( ColumnA = lf_search ).
INSERT table1_bis FROM wa_table1_bis.
* Then SELECT with INNER JOIN
SELECT a~ColumnA
  FROM table1 AS a
  INNER JOIN table1_bis AS b
    ON b~Column1 CP a~Column1
  INTO TABLE @DATA(lt_table1).

(this is a guess... please check, I'm not truly sure this works as is)

Answer 2

You can also loop the DB table, which is just another flavor of what @cape_bsas suggested:

DATA: itab TYPE TABLE OF table1-ColumnA.

SELECT ColumnA, 'Newyork' AS pattern
  FROM table1
  INTO @DATA(line).
  CHECK line-pattern CP line-ColumnA.
  APPEND line-ColumnA TO itab.
ENDSELECT.