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: 

ignore case in SELECT statement

Former Member
0 Kudos

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: ztablefield = 'ABC_123'; zothertablefield = 'abc_123';

I have to use this field in WHERE clausule:

SELECT a501kschl a501knumh 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 a501userf1_txt, but it is not possible in this situation. (I should translate to upper case the a501userf1_txt before the WHERE clausule [??])

Any ideas? Tips?

1 ACCEPTED SOLUTION

uwe_schieferstein
Active Contributor

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

5 REPLIES 5

uwe_schieferstein
Active Contributor

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
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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

Rob

Former Member
0 Kudos

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