Case sentivity issue

[Former Member]

Hi all,

I have an issue related to case sensitivity.

I have a screen parameter "s_id_txt for swwwihead-wi_text."

I have to retrieve data from swwwihead table using this field,but it is case sentive

the query is as

select .....from swwwihead where wi_text in s_id_txt

How do i make the parameter case-insensitive?

Pls. post ur ideas.

Thanks.

stock

[Former Member]

SELECT-OPTIONS: s_id_txt for swwwihead-wi_text lower case.

[Former Member]

hi STOCK,

hope case shudn't be a probelm in the query ..

in case it is .. you can use..

 select-options : s_id_txt for swwwihead-wi_text lower case.

regards

satesh

[Former Member]

Hi,

You can use TRANSLATE YO UPPER CASE.

in AT SELECTION-SCREEN event, convert the contents of s_id_txt o upper case and then pass it to select querry.

Hope this helps...

Regards,

Shashank

[Former Member]

Hi ,

Try using s_id_txt for swwwihead-wi_text lower case.

This will allow both lower and upper case values.

Thanks and Regards ,

Sriranjani Chimakurthy.

[Former Member]

HI,

You can do it like this.

select-options : s_id_txt for swwwihead-wi_text lower case .

OR

select-options : s_id_txt for swwwihead-wi_text Upper case . Depending upon ur reqmt.

Cheers

Sunny

[Former Member]

HI STOCK,

If u want to restrict the user from entering in upper case.then use this.

select-options : s_id_txt for swwwihead-wi_text lower case.

hope this helps,

thanks,

priya

[vinod_gunaware2]

Upper and lower case for selection criteria:

SELECT-OPTIONS <seltab> FOR <f> ... LOWER CASE ..............

Upper and lower case for selection criteria:

SELECT-OPTIONS <seltab> FOR <f> ... LOWER CASE ..............

regards

vinod

[Former Member]

Hi all

The addition of lower case/upper case does not solve my problem.

If I give the input as "*abc" , the query has to retrieve all the data containing "abc" regardless or its case ie "....ABC" ,"...abc".

Pls. post ur ideas.

Thanks.

stock

[former_member186741]

This is not an issue as the field is tagged as lower case in the dictionary, it is automatically case-sensitive.

Therefore the values you enter on the selection screen will retain their case within your program. Go into debug mode and check it out.

If you are having problems reading records with the values you are entering on the screen this may be because you really want the IF to find text LIKE your entry rather than exactly EQUAL to it. TRy putting '*'s round your text on the screen.

[former_member186741]

please show us the sql select statement you are using.

[Former Member]

HI Stock,

then use..

select * into table itab from table..

and then use..

<b>loop at itab where <f> CP s_d_txt.

move itab to itab1.

append itab1.

endloop.</b>

this would work..

regards

satesh

[former_member186741]

sorry, Stock but I have not read your problem properly. At least I wasn't the only one! In fact, you want the exact opposite of what everybody has been suggesting.

Your problem is pretty complex as you need to compare each character to its upper and lower case equivalents.

The only way I can think of is to read every item into an internal table. Translate them all to upper case (TRANSLATE statement) and then search the internal table for input string (which itself should be in UPPER case too). Not very effiecent but ok if you have few records to scan.

[former_member186741]

Hi Stock, just found (thanks to Rob Burbank) on the forum via another similar thread that there is an option which may be of use to you. You can use 'native' sql, here's Rob's answer:

report ztest no standard page heading.

tables lfa1.

data: name like lfa1-name1 value 'N HARRISON ST'.

EXEC SQL.

SELECT *

INTO :LFA1

FROM LFA1

WHERE UPPER(NAME1) = :NAME

ENDEXEC.

................

Apparently there are some performance concerns, because I guess sql has to do what I suggested earlier itself and convert everything so that the comparision could be made. Still it might do the trick for you.

[Former Member]

I'm not sure this will work exactly correctly. The poster says parameter, but it looks like he is actually creating a select option. I'm not sure you can use select options in native SQL. But something like this might work.

There are just under 24,000 records in this table in our system. Reading the whole table took under 15 seconds, so I'd have a look at your first suggestion (translate).

Rob

Message was edited by: Rob Burbank

[Former Member]

Hi all,

me too looking for solutions for this kind of problem,

i guess i found Neil Woodruff answer some way ok for time beeing ..saying that

"read every item into an internal table. Translate them all to upper case (TRANSLATE statement) and then search the internal table for input string (which itself should be in UPPER case too). Not very effiecent but ok if you have few records to scan.

I am using ECC 5, and i tried executing native SQL using

EXEC SQL.

SELECT *

INTO :LFA1

FROM LFA1

WHERE UPPER(NAME1) = :NAME

ENDEXEC.

but some way i am getting error message "An SQL error occurred when executing a Native SQL command"

so please let me know what is the correct statement to execute this.

Thanks

Murali.

[Former Member]

What database system are you using?

rob

[Former Member]

I am not sure if this is efficient. But take a look at it.

TABLES: lfa1.

SELECT-OPTIONS: so_name1 FOR lfa1-name1 LOWER CASE. "Vendor Name

DATA: i_lfa1  TYPE TABLE OF lfa1, "Itab
      wa_lfa1 TYPE lfa1,          "Work Area For Itab
      dbcur   TYPE cursor.        "DB Cursor

*-Open Cursor
OPEN CURSOR dbcur FOR
  SELECT * FROM lfa1 ORDER BY lifnr.

*-Fetch Rows Using Cursor & Append To Itab
DO.
  FETCH NEXT CURSOR dbcur INTO wa_lfa1.
  IF sy-subrc <> 0.
    EXIT.
  ENDIF.

  TRANSLATE: wa_lfa1-name1 TO LOWER CASE.
  IF wa_lfa1-name1 IN so_name1.
    APPEND wa_lfa1 TO i_lfa1.
    CLEAR  wa_lfa1.
  ENDIF.
ENDDO.

*-Close Cursor
CLOSE CURSOR: dbcur.

[Former Member]

Case sensitve db retrievals are never too easy. If the table is not big, I suggest you get the records satisfying other criteria into an internal table and then eliminate the one that don't satisfy your text condition in a loop and using TRANSLATE.