Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Like operator in a select

Former Member
0 Kudos

Hi,

I need to compare with a like operator in a select.

For example, I have the following:

  select matnr into ematnr from mast client specified

  WHERE mandt = sy-mandt

  AND werks EQ '3000'

  AND stlan EQ '1'

  AND stlal NE  '01'

  AND matnr like 'FA%'.

Can anybody tell how I can get this, the last AND.

In a string it would be something like this but, how it is in a select:

matnr+0(2) = 'FA'.

Thanks in advance.

Regards.

20 REPLIES 20

Former Member
0 Kudos

Customers with a 'p' in their name from the second position:


SELECT name, city FROM customer

WHERE name LIKE '_%p%'

Please go through the link.

http://www.sapdb.org/7.4/htmhelp/8c/ccce27c71c11d2a97100a0c9449261/content.htm

former_member184569
Active Contributor
0 Kudos

use matnr CS 'FA'

This checks the condition if the string 'FA' is present in the variable matnr.

0 Kudos

Shame it's not syntactically correct.

0 Kudos

Yes, you are right CS logical expression does not work in select queries. Should have checked that before posting. My mistake. Sorry about that.

And ironically, David, your code works perfectly fine after testing.

The only discrepancy I can see is, you need to either use end select, or select single, if ematnr is a work area or use the addition into table ematnr if it is a table. (Which I guess might have been already taken care of too).

former_member388650
Participant
0 Kudos

data: w_str like mara-matnr.

w_str = 'FA*'.

select matnr into ematnr from mast client specified 

  WHERE mandt = sy-mandt

  AND werks EQ '3000'

  AND stlan EQ '1'

  AND stlal NE  '01'

  AND matnr CP w_str.     "CP for Contains pattern

Hope it works.

Regards,

Vishram

0 Kudos

You hope it works. Hmm. Shame it's not syntactically correct.

The idea of this site is to post helpful answers. Not just what you think might work. If you don't know, then don't post.

Former Member
0 Kudos

Hi David,

Use CS - 'Contains String' Operator.

select matnr into ematnr from mast client specified

  WHERE mandt = sy-mandt

  AND werks EQ '3000'

  AND stlan EQ '1'

  AND stlal NE  '01'

  AND matnr CS 'FA'.

For detailed understanding read http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3516358411d1829f0000e829fbfe/content.htm.

BR.

matt
Active Contributor
0 Kudos

Shame it's not syntactically correct.

The idea of this site is to post helpful answers. Not just what you think might work. If you don't know, then don't post.

0 Kudos

Hi Matthew,

Thanks a lot for correcting me.

Hi David,

Syntactically correct and working code is,

data srch_str TYPE c LENGTH 2 value 'FA'.

CONCATENATE '%' srch_str '%' INTO srch_str.

select matnr into ematnr from mast client specified

   WHERE mandt = sy-mandt

   AND werks EQ '3000'

   AND stlan EQ '1'

   AND stlal NE  '01'

   AND matnr like srch_str.

   endselect.

BR.

asik_shameem
Active Contributor
0 Kudos

Hi David,

The mentioned statement itself will work.

  select matnr into ematnr from mast client specified

  WHERE mandt = sy-mandt

  AND werks EQ '3000'

  AND stlan EQ '1'

  AND stlal NE  '01'

AND matnr like 'FA%'. " this statement will work for your requirement

Regards,

Asik.

matt
Active Contributor
0 Kudos

Precisely. I do wonder whether we've understood the OP's question. A simple look in the ABAP help at "like" would have revealed this.

0 Kudos

I still have that question in my mind...Is he looking for something else..

0 Kudos

His code is correct and it is working,

I think, he is asking how we get FE by using like 'FE%'  in select statement.

select matnr into ematnr from mast client specified

  WHERE mandt = sy-mandt

  AND werks EQ '3000'

  AND stlan EQ '1'

  AND stlal NE  '01'

  AND matnr like 'FA%'.   " How it works ?

former_member209120
Active Contributor
0 Kudos

Hi David,

Your Code working fine

data : ematnr type mast-matnr.

select matnr into ematnr from mast client specified
   WHERE mandt = sy-mandt
   AND werks EQ '3000'
   AND stlan EQ '1'
   AND stlal NE  '01'
   AND matnr like 'CH%'.
  endselect.

Output



See this like you can understand how you get  matnr+0(2) = 'FA' for last and matnr like 'FA%'.
http://help.sap.com/saphelp_nw70/helpdata/en/dc/a4dc72e89811d5995600508b6b8b11/content.htm

Former Member
0 Kudos

Hi David,

Your statement need little syntax correction.

select matnr into ematnr from mast client specified

   WHERE mandt = sy-mandt

   AND werks EQ '3000'

   AND stlan EQ '1'

   AND stlal NE  '01'

   AND matnr like 'FA%'.

ENDSELECT.

However you can check the wildcard character.

In WHERE-conditions of Open SQL with the operator LIKE "%" stands for any character strings and "_" for individual characters. 

Logically it should work.

Thanks,

Anurag

former_member195402
Active Contributor
0 Kudos

Hi,

please try SELECT SINGLE or SELECT INTO TABLE. For example

  select SINGLE matnr into ematnr from mast 

  WHERE werks EQ '3000'

  AND stlan EQ '1'

  AND stlal NE  '01'

  AND matnr like 'FA%'.

Do not use CLIENT SPECIFIED  with sy-mandt.

Also do not use SELECT ENDSELECT in this case.

Regards,

Klaus

former_member198834
Participant
0 Kudos

hi try this

1. data : l_matnr type matnr,

              l_str(2) type c.

2.   select matnr into ematnr from mast client specified

  WHERE mandt = sy-mandt

  AND werks EQ '3000'

  AND stlan EQ '1'

  AND stlal NE  '01'.

if sy-subrc = 0.

l_str = ematnr+0(2).

if l_str = 'FA'.

move ematnr to l_matnr.

endif.

endif.

suresh


0 Kudos

His code is correct and it is working,

I think, he is asking how we get FE by using like 'FE%'  in select statement.

select matnr into ematnr from mast client specified

  WHERE mandt = sy-mandt

  AND werks EQ '3000'

  AND stlan EQ '1'

  AND stlal NE  '01'

  AND matnr like 'FA%'.   " How it works ?

raymond_giuseppi
Active Contributor
0 Kudos

I agree with previous posts, nobody seems able to understand your question (me too) so what did you expect, comparing sql_cond - LIKE with log_exp - Comparison Operators for Character-Like Data Types (e.g. CP Covers Pattern) or what ?

Regards,

Raymond

0 Kudos

Hi David,

I am unable to understand your question clearly.From what I am able to understand you are trying to understand the use of Like Operator in Select Queries.The Select Statement written by you is fine.But it needs a little modification.I'll show you a few examples of Like Operators in Select Queries and see if it answers your question.

Sample Program illustrating the use of Like Operators in Where Clause of Select Queries:

   types: begin of ty_mast,
       matnr type mast-matnr,
       end of ty_mast.


       data:it_mast type table of ty_mast ,
             wa_mast type ty_mast.


select matnr into table it_mast from mast.

skip 1.
write : / 'Table Contents'.


loop at it_mast into wa_mast.

write : / wa_mast-matnr.

endloop.


select matnr into table it_mast from mast

  where matnr like '_I%'.

skip 2.
write : / 'Table Contents where Material Number contains Alphabet I'.


loop at it_mast into wa_mast.

write : / wa_mast-matnr.

endloop.



select matnr into table it_mast from mast

  where matnr like '_T'.

skip 2.
write : / 'Table Contents where Material Number  ends with Alphabet T'.


loop at it_mast into wa_mast.

write : / wa_mast-matnr.

endloop.


select matnr into table it_mast from mast

  where matnr like 'R_'.

skip 2.
write : / 'Table Contents where Material Number  starts with Alphabet R'.


loop at it_mast into wa_mast.

write : / wa_mast-matnr.

endloop.


select matnr into table it_mast from mast

  where matnr like 'E%'.


skip 2.
write : / 'Table Contents where Material Number  starts with Alphabet E'.


loop at it_mast into wa_mast.

write : / wa_mast-matnr.

endloop.


select matnr into table it_mast from mast

  where matnr like '%1'.

skip 2.
write : / 'Table Contents where Material Number  ends with  1'.


loop at it_mast into wa_mast.

write : / wa_mast-matnr.

endloop.

Underscore represents a single character.

Percentage represents any sequence of characters.

Explanation of Conditions used in where Clause:

1)  _I%  : The Material Number contains the Alphabet I in the second position.The First position can be any character.After second postion the Material Number can have any sequence of characters including Blank or Space.

2) _T : The Material Number contains the Alphabet T in the second position.The First position can be any character.In other words the Material Number can consist only of 2 characters and should end with T.

3) R_ :The Material Number contains the Alphabet R in the first position.The second position can be any character.In other words the Material Number can consist only of 2 characters and should start with R.

4) E% : The Material Number starts with letter E.The remaining posistions can contain any sequence of characters including Blank or Space.

5) %1 : The Material Number ends with character 1.The remaining posistions can contain any sequence of characters including Blank or Space.

Report Output: