topic Re: Select statement that ignores case in Application Development and Automation Discussions

Select statement that ignores case

Former Member — Tue, 22 Apr 2008 15:18:03 GMT

Hello,

is there a way to use the select open SQL statement that ignores case.

I am trying get values from T005F and unless I enter BEZEI in the same format it is stored in it does not return a value. Say BEZEI is 'Hertfordshire' , if I try it with hertfordshire or HERTFORDSHIRE it returns nothing.

Thanks

Re: Select statement that ignores case

Former Member — Tue, 22 Apr 2008 15:25:33 GMT

Unfortunetly no for that table. Some tables have a search field that the case is all upper for this purpose (KNA1).

Only way I've done this is with something like this.


SELECT * into WK_REC from T005F
  WHERE <any other condtion>.
   WK_BEZEI = WK_REC-BEZEI.
   TRANSLATE WK_BEZEI to UPPER CASE.
   CHECK WK-BEZEI IN SCREEN_RANGE.
   APPEND WK_REC to YOUR_IT.
ENDSELECT.

You'll also want to make sure the DOMAIN for your SCREEN_RANGE does not allow Lower Case, or you could loop thru the range and translate them all before you start the select.

Edited by: Paul Chapman on Apr 22, 2008 5:28 PM

Re: Select statement that ignores case

Former Member — Tue, 22 Apr 2008 15:32:15 GMT

I've had problems with this in the past too. SAP doesn't consider the texts of counties to be a searchable field so doesn't hold it in upper case anywhere. You have a few options:

1. Create a new table and a program to populate this table, copy all entries from T005F and add a new field to hold the text in uppercase. You can then use this to search. This obviously creates some maintenance issues but in theory counties shouldn't change too much!

2. Try removing the first character of your county and use that to search with, i.e. %ertfordshire and use LIKE in the select statement instead of EQ to find it.

These aren't ideal solutions but I hope they help.

Gill

Re: Select statement that ignores case

Former Member — Tue, 22 Apr 2008 15:38:10 GMT

Hi,

don't really want to get into creating another table to store countries, I think I will end up creating my own internal table and then changing values in there into uppercase, and my my values in uppercase and see if I can get a match. Can't think of a better way.

Thanks