Case Insensitive Select Statement

[Former Member]

Hi all,

I have requirement that i have to fetch data from database table without considering whether it is upper case or lower case. For example, if user is giving Name1 on selection screen as "Micro", i want all record from database whether it is "micro" or "MICRO" or "mICro" and so on. Please guide me.

Thanks

Braham Mittal

[Former Member]

Looks like Problem is not with your select statement,

Just check the Domain of the concerned field. There will be a characteristic check box called "lower case" in the Definition tab of your domain. See if that is Flagged. If that is flagged, then the stored record will be case sensitive. In best practice, you should not have that flagged, So that the records will not be case sensitive and you will not have to face a similar situation.

Hope this helps.

Let me know if you need any help.

Vinodh Balakrishnan

[Former Member]

Hello Braham,

Apart from Vinodh's suggestion, if there are records already, then

probably you can think

1. Reading the records into an Internal table(probably any additional filtering might help to reduce data).

2. Convert the Name1 field to UPPERCASE by using TRANSLATE statement

3. Do your comparision in UPPER case.

It's not a nice way but probably you can keep it as an option....

I think you should do it if the number of records are not that really large...

Hope this helps.

Best Regards,

Subhakanth

[Former Member]

Hi Braham,

Here is one way to code case insensitive select statemets...

To be more accurate also check the string length of the user inputs with that of the corresponding database field... In reference to your example, check the strlen(micro) with its counterpart in the database tables...

REPORT zmamata NO STANDARD PAGE HEADING .

DATA : wa_adrp TYPE adrp,

it_adrp TYPE TABLE OF adrp .

PARAMETERS : p_name TYPE adrp-name_text .

SELECT * FROM adrp

INTO TABLE it_adrp

WHERE name_text NE space .

LOOP AT it_adrp INTO wa_adrp .

IF wa_adrp CS p_name.

WRITE : / wa_adrp-name_text , wa_adrp-persnumber .

ENDIF.

CLEAR : wa_adrp.

ENDLOOP.

Do reward points if this suggestion helps you.

Regards,

Mamata Swamy.

Edited by: Mamata Swamy on Jan 4, 2008 6:28 AM

[seshatalpasai_madala]

Hi,

Open SQL does not support this, you cannot write a select statement which will compare aginst a database record in case insensitive manner. What you can do at the most is convert your variable to UPPER CASE and compare aginst the database filed if the database field is case insensitive.

But open SQL does not support this if the database field is case sensitve. You cannot compare a case sensitive filed in the database ignoring the case. I found this strange but its the fact.

Regards,

Sesh

[ajay_rajareddy]

Ok.. what you said is correct...

then there is some data in tables like ....Havana and HAVANA... both are available in tables ..

then how convert directly..to uppercase or and lowercase.. then data will fetched from database was completely wrong