topic Re: Using LIKE in SELECT ..... WHERE in Application Development and Automation Discussions

Using LIKE in SELECT ..... WHERE

Former Member — Wed, 01 Jun 2005 19:23:48 GMT

Hi,

I need to do pattern matching during SELECT so that a table field contains or is the same as an input variable. How can I accomplish this?

Regards,

Nanditha

Re: Using LIKE in SELECT ..... WHERE

RichHeilman — Wed, 01 Jun 2005 19:41:13 GMT

You can use SELECT-OPTIONS for your select screen.

Then in your select statement use the IN operator.



data: imakt type table of makt with header line.

select-options: s_maktx for imakt-maktx.

select * from makt
         into corresponding fields of table imakt
                   where maktx in s_maktx.

or you can "hardcode" the patterns



* This code selects all material groups that start with 
* DRA or DWA

data: begin of i_matkl occurs 0,
      matkl type mara-matkl.
data: end of i_matkl.

  select matkl from  t023
          into corresponding fields of table i_matkl
                  where ( matkl like 'DRA%'
                    or matkl like 'DWA%' ).

Regards,

Rich Heilman

Re: Using LIKE in SELECT ..... WHERE

ssimsekler — Wed, 01 Jun 2005 19:42:14 GMT

Hi Nanditha

You can use LIKE as follows:

SELECT * FROM mara
       INTO TABLE lt_mara
       WHERE matnr LIKE '%AD%' .

Regards

*--Serdar <a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=qbk%2bsag%2bjiw%3d">[ BC ]</a>

Rich, you are here again

Message was edited by: Serdar Simsekler

Re: Using LIKE in SELECT ..... WHERE

Former Member — Wed, 01 Jun 2005 19:43:46 GMT

Example to select all customers whose name begins with 'M':

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM_WA

WHERE NAME LIKE 'M%'.

you can find more examples under :

Logical Condition in Database Access Statements.

May be this is what you were looking for.

Re: Using LIKE in SELECT ..... WHERE

RichHeilman — Wed, 01 Jun 2005 19:44:50 GMT

Here is the "help" for the syntax of LIKE in SELECT statement.

<i>

f [NOT] LIKE g

Addition:

... ESCAPE h

Effect

The condition is met for a table entry if the statement "f (does not) equal the pattern in g" is true for the values of f and g. f must always be a field descriptor, and g an ABAP field. If f has the value NULL, then the result of the check for the statement is unknown. Within a pattern, there are two special characters:

'_' (underscore) stands for any single character.

'%' (percentage sign) stands for any sequence of characters, including an empty string.

Examples

Example to select all customers whose name begins with 'M':

TABLES SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM

WHERE NAME LIKE 'M%'.

WRITE: / SCUSTOM-ID, SCUSTOM-NAME.

ENDSELECT.

Example to select all customers whose name contains 'huber':

TABLES SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM

WHERE NAME LIKE '%huber%'.

WRITE: / SCUSTOM-ID, SCUSTOM-NAME.

ENDSELECT.

Example to select all customers whose name does not contain 'n' as the second character:

TABLES SCUSTOM.

SELECT ID NAME FROM SCUSTOM

INTO CORRESPONDING FIELDS OF SCUSTOM

WHERE NAME NOT LIKE '_n%'.

WRITE: / SCUSTOM-ID, SCUSTOM-NAME.

ENDSELECT.

Notes

LIKE can only be used for alphanumeric database fields. In other words, table field f must have Dictionary type ACCP, CHAR, CLNT, CUKY, LCHR, NUMC, UNIT, VARC, TIMS or DATS. The comparison field g must always have type C.

The maximum length of the pattern is 2n - 1 characters, where n is the length of field f.

Trailing spaces are ignored in comparison field g. If a pattern contains trailing spaces, you must enclose it in single inverted commas ('). If your pattern is enclosed in inverted commas and you also want to include inverted commas as part of the pattern, the inverted commas in the pattern must be doubled.

You cannot use this variant in the ON addition to the FROM clause.

</i>

Regards,

Rich Heilman

Re: Using LIKE in SELECT ..... WHERE

RichHeilman — Wed, 01 Jun 2005 19:47:40 GMT

Serdar,

I have been working on J2ee stuff for quite a while. It has been keeping me busy and very frustrated. But now, I should have some more time to contribute to SDN. Don't know how you and Anand find the time.

Regards,

Rich Heilman

Re: Using LIKE in SELECT ..... WHERE

ssimsekler — Wed, 01 Jun 2005 19:59:37 GMT

Hi Rich

During the day I seldomly connect to SDN since our work computers are not connected to the internet for security. So, I am nonstop online at nights here in Turkey and it is day time in the USA.

It should be really good that you deal with new innovations. But like in all software business it can make anyone really angry. I believe you can handle it.

May it be easy!

Regards

*--Serdar <a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=qbk%2bsag%2bjiw%3d">[ BC ]</a>

Re: Using LIKE in SELECT ..... WHERE

Former Member — Wed, 01 Jun 2005 20:01:58 GMT

I am not in a position to hardcode or use a select-option for the input.

Re: Using LIKE in SELECT ..... WHERE

Former Member — Wed, 01 Jun 2005 20:03:52 GMT

My req is something like if the user enters a first name or a last name and I have a table field with the full name, how do I select that particular full name using the first/last name?

Re: Using LIKE in SELECT ..... WHERE

ssimsekler — Wed, 01 Jun 2005 21:09:25 GMT

Hi Nanditha

OK, the LIKE addition should solve your problem. Try something like this:


REPORT Zlike_example .
PARAMETERS: p_name(40) TYPE c .

DATA lv_srch_str(60) TYPE c .
DATA lv_ename LIKE p0001-ename .

CONCATENATE '%' p_name '%' INTO lv_srch_str .

SELECT ename FROM PA0001
       INTO lv_ename
       WHERE ename LIKE lv_srch_str .

  WRITE:/ lv_ename .

ENDSELECT .

By the way, do not forget to reward points to helpful posts and close the thread if solved.

Regards

*--Serdar <a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=qbk%2bsag%2bjiw%3d">[ BC ]</a>

Re: Using LIKE in SELECT ..... WHERE

Former Member — Thu, 02 Jun 2005 21:35:27 GMT

Thanks, that helped.

Re: Using LIKE in SELECT ..... WHERE

Former Member — Thu, 02 Jun 2005 21:38:02 GMT

How do I reward points and close the thread?

Re: Using LIKE in SELECT ..... WHERE

ssimsekler — Thu, 02 Jun 2005 21:42:52 GMT

Hi Nanditha

You can use the scala on the left of each post to reward points. And if you reward a 10-point then your thread is closed.

But, it seems there may be a problem at the system. If you can not do this by now (i.e. if you can not see the scala radiobuttons), you can do this later. Do not forget to close the thread to save SDNers times.

Regards

*--Serdar <a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=qbk%2bsag%2bjiw%3d">[ BC ]</a>