topic Re: Case insensitive search in select statement in Application Development and Automation Discussions

Case insensitive search in select statement

Former Member — Mon, 08 Mar 2010 11:09:17 GMT

Hello Experts,

i have select over table RSAABAP

WHERE line LIKE '%ABC%', because LINE has "data Element" EDPLINE with mark "Lower Case" , the result will be case sensitive.

It means that i don't find the row with e.g. 'abc' .

i would like to use case insensitive.

one solution is add rows from SAP table to internal and use TRANSLATE to upper case and search int. table again.

is existing another solution ?

Thanks for help in advance

Martin

Re: Case insensitive search in select statement

SuhaSaha — Mon, 08 Mar 2010 11:21:23 GMT

Hello Martin,

Native SQL may be an option but it has its own limitations.

SAP documentation on using Native SQL in ABAP: [http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm|http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm]

Please check Bart & Rob's response to this thread:

BR,

Suhas

Edited by: Suhas Saha on Mar 8, 2010 4:53 PM

Re: Case insensitive search in select statement

former_member770378 — Mon, 08 Mar 2010 14:48:05 GMT

Did you try

TRANSLATE TO UPPER CASE

TRANSLATE TO LOWER CASE

before the select ?

Re: Case insensitive search in select statement

former_member156446 — Tue, 09 Mar 2010 01:21:12 GMT

My blog>>> https://wiki.sdn.sap.com/wiki/x/6gGHBQ -get all case of a word

Re: Case insensitive search in select statement

Former Member — Tue, 09 Mar 2010 03:00:42 GMT

J@Y - does your method work for something like "General Motors"?

Rob

Re: Case insensitive search in select statement

Former Member — Tue, 09 Mar 2010 04:10:22 GMT

Hi Rob,

J@Y's method seems faulty to me. The coding he posted basically creates a range with strlen entries, where for the i-th entry the i-th character is upper case. I'm not sure if the initial intent was to create a range with all possible combinations, but this doesn't sound like a good solution to me (too many combinations possible).

In this special case of a search string with %ABC% it wouldn't be too bad, because we'd end up with 8 combinations:


%abc%
%Abc%
%aBc%
%ABc%
%abC%
%AbC%
%aBC%
%ABC%

Here's some coding that would generate a table with all text strings (could be easily modified to build a range). However, for a string like General Motors you wouldn't want to apply this, because this leads to way too many combinations (2^strlen to be precise, so 8,192 for <i>General Motors</i>):


TYPES:
  t_text     TYPE text40,
  t_text_tab TYPE STANDARD TABLE OF t_text.

FORM create_variations USING    value(text) TYPE t_text
                       CHANGING text_tab    TYPE t_text_tab.

  FIELD-SYMBOLS <text> TYPE t_text.
  DATA:
    rest     TYPE t_text,
    rest_tab TYPE t_text_tab,
    lc       TYPE char1,
    uc       TYPE char1.

  TRANSLATE text TO LOWER CASE.

  lc = uc = text(1).
  TRANSLATE uc TO UPPER CASE.

  sy-fdpos = STRLEN( text ).
  IF sy-fdpos = 1.
    APPEND lc TO text_tab.
    IF lc &lt;&gt; uc.
      APPEND uc TO text_tab.
    ENDIF.
    EXIT.
  ENDIF.

  rest = text+1.
  PERFORM create_variations USING    rest
                            CHANGING rest_tab.

  LOOP AT rest_tab ASSIGNING <text>.
    CONCATENATE lc <text> INTO rest RESPECTING BLANKS.
    APPEND rest TO text_tab.
    IF lc &lt;&gt; uc.
      CONCATENATE uc <text> INTO rest RESPECTING BLANKS.
      APPEND rest TO text_tab.
    ENDIF.
  ENDLOOP.

ENDFORM.

Cheers, harald

Re: Case insensitive search in select statement

Former Member — Tue, 09 Mar 2010 06:46:59 GMT

FYI

I used the code below (check with CP instead of LIKE 😞

select distinct infocube line

into corresponding fields of la from zv_where_use

WHERE LINE LIKE l_find_string

order by infocube.

check la-line cp l_find_string_report.

...

endselect.

Re: Case insensitive search in select statement

Former Member — Tue, 09 Mar 2010 13:57:13 GMT

I don't think that will work either. So long as your database supports the "TRANSLATE TO UPPER CASE" command (DB2 and Oracle do), your best bet is native SQL.

Rob

Re: Case insensitive search in select statement

Former Member — Thu, 17 Jun 2010 12:41:02 GMT

Searching for strings (parts or with pattern) is cumbersome if the table field isn't defined as case-insensitive.

Example:

Search all requests in "E07T" where "AS4TEXT" contains "Sales."

With a normal SELECT * FROM E070T WHERE AS4TEXT LIKE gv_text" values like SALES or sales will not be found.

By using EXEC-SQL, there's a way to do this selection Case-insensitive:

Code

REPORT xy.

DATA:

gt_e07t TYPE TABLE OF e07t WITH HEADER LINE,

gv_reqtxt(60).

PARAMETERS p_text TYPE rzielort.

Selection by Request text
Native SQL needed since as4text is case sensitive but we
want to search case-insensitive

gv_reqtxt = p_text.

TRANSLATE gv_reqtxt TO UPPER CASE.

CONCATENATE '%' gv_reqtxt '%' INTO gv_reqtxt.

Change SAP-wildcards to DB-wildcards TRANSLATE gv_reqtxt USING '*%'.

TRANSLATE gv_reqtxt USING '+_'.

EXEC SQL PERFORMING append_e07t.

SELECT * FROM e07t INTO :gt_e07t

WHERE upper(as4text) LIKE :gv_reqtxt ENDEXEC.

DESCRIBE TABLE gt_e07t LINES sy-dbcnt.

CHECK sy-dbcnt GT 0.

LOOP AT gt_e07t.

WRITE: / gt_e07t-trkorr, gt_e07t-as4text.

ENDLOOP.

&----

*& Form append_e07t

&----

text

----

FORM append_e07t.

APPEND gt_e07t.

ENDFORM. "append_e07t