ignore case in SELECT statement

[Former Member]

Hello, is there some possibility, to ignore case while comparing text values in WHERE clausule? In one table, field can be in lower case in second in can be in upper case. Example: ztable_{field = 'ABC_123'; zothertable}field = 'abc_123';

I have to use this field in WHERE clausule:

SELECT a501_{kschl a501}knumh a501~werks

INTO (kschl, knumh, a501_werks)

FROM a501

WHERE a501~userf1_txt = userf1_txt "ignore case

AND a501~datab <= datum

AND a501~datbi => datum

AND a501~werks = werks.

ENDSELECT.

I had an idea, to translate userf1_txt to upper case, and then do the same with a501_{userf1_txt, but it is not possible in this situation. (I should translate to upper case the a501}userf1_txt before the WHERE clausule [??])

Any ideas? Tips?

[Former Member]

Hello, is there some possibility, to ignore case while comparing text values in WHERE clausule? In one table, field can be in lower case in second in can be in upper case. Example: ztable_{field = 'ABC_123'; zothertable}field = 'abc_123';

I have to use this field in WHERE clausule:

SELECT a501_{kschl a501}knumh a501~werks

INTO (kschl, knumh, a501_werks)

FROM a501

WHERE a501~userf1_txt = userf1_txt "ignore case

AND a501~datab <= datum

AND a501~datbi => datum

AND a501~werks = werks.

ENDSELECT.

I had an idea, to translate userf1_txt to upper case, and then do the same with a501_{userf1_txt, but it is not possible in this situation. (I should translate to upper case the a501}userf1_txt before the WHERE clausule [??])

Any ideas? Tips?

[uwe_schieferstein]

Hello Marcin

I would create a <b>RANGE </b>itab and add the two possibles values:

DATA:
  lt_rng   TYPE RANGE OF a501-userf1_txt,
  ls_rng   LIKE LINE OF lt_rng.


  ls_rng-sign  = 'I'.
  ls_rng-option = 'EQ'.
  ls_rng-low     = 'abc_123'.
  APPEND ls_rng TO lt_rng.
  TRANSLATE ls_rng-low TO UPPER CASE.
  APPEND ls_rng TO lt_rng.

SELECT a501~kschl a501~knumh a501~werks
INTO (kschl, knumh, a501_werks)
FROM a501
WHERE a501~userf1_txt IN lr_rng   " case-insensitive
AND a501~datab <= datum
AND a501~datbi => datum
AND a501~werks = werks.
ENDSELECT.

Regards

Uwe

[Former Member]

hi,

1. first of all u have to determine in which table it is upper case and in which 1 it is lower case.

2.I think this value is provide by user or is it HARD CODED.

3. if it is HARD CODE , go by UWE soloution.

4. if by user i/p , try to use the keywords TRANSLATE.

Rgds

Reshma

[former_member189059]

Check this code and output

This is the output

Rm167 is matched by pattern #R#m167

rm167 is not matched by pattern #R#m167

RM167 is not matched by pattern #R#m167

rM167 is not matched by pattern #R#m167

using this code:

FORM ANY .
DATA:
  lr_text TYPE RANGE OF text40 with header line,
  lv_text1 type text40 value 'Rm167',
  lv_text2 type text40 value 'rm167',
  lv_text3 type text40 value 'RM167',
  lv_text4 type text40 value 'rM167'.
  lr_text-sign = 'I'.
  lr_text-option = 'CP'.
  lr_text-low = '#R#m167'.
  APPEND lr_text.
 
  IF lv_text1  IN lr_text.
    WRITE: / lv_text1, 'is matched by pattern', lr_text-low.
  ELSE.
    WRITE: / lv_text1, 'is not matched by pattern', lr_text-low.
  ENDIF.
  IF lv_text2  IN lr_text.
    WRITE: / lv_text2, 'is matched by pattern', lr_text-low.
  ELSE.
    WRITE: / lv_text2, 'is not matched by pattern', lr_text-low.
  ENDIF.
  IF lv_text3  IN lr_text.
    WRITE: / lv_text3, 'is matched by pattern', lr_text-low.
  ELSE.
    WRITE: / lv_text3, 'is not matched by pattern', lr_text-low.
  ENDIF.
  IF lv_text4  IN lr_text.
    WRITE: / lv_text4, 'is matched by pattern', lr_text-low.
  ELSE.
    WRITE: / lv_text4, 'is not matched by pattern', lr_text-low.
  ENDIF.
 
ENDFORM.                    " any

[Former Member]

Unfortunately, I think your only option is native SQL. Even then it will depend on your database.

Rob

[Former Member]

Ok, Uwe solved the problem, I only make some modification in this code. Thanks everybody!