Like operator in a select

[Former Member]

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.

[Former Member]

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.

[Former Member]

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 Member]

use matnr CS 'FA'

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

[matt]

Shame it's not syntactically correct.

[Former Member]

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 Member]

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

[matt]

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]

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]

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]

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]

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]

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.

[asik_shameem]

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

[former_member209120]

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]

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]

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 Member]

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 Member]

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

[former_member209120]

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 ?

[RaymondGiuseppi]

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

[Former Member]

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: