SQL where clause case intensitive criteria

Former Member — Thu, 05 Mar 2009 10:12:18 GMT

Hai all i need to search zopleidingen the clause in the where needs to be case insensitive

I want to get all records for zopleidingen~zopleidingsnaam LIKE lv_opleiding

not depending on the fact that zopleidingen~zopleidingsnaam is upper or lower case.

I tried to upper the lv_opleiding but how can i make the LIKE statement to seacrh voor uppercased lv_opleiding??

DATA: lt_resultaten TYPE TABLE OF zopleidingen,

lv_opleiding TYPE string.

CONCATENATE '%' 'h' '%' INTO lv_opleiding.

TRANSLATE lv_opleiding TO UPPER CASE.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE lt_resultaten

FROM zopleidingen

WHERE zopleidingen~begda <= sy-datum

AND zopleidingen~endda >= sy-datum

AND zopleidingen~zopleidingsnaam LIKE lv_opleiding.

Re: SQL where clause case intensitive criteria

Former Member — Tue, 17 Mar 2009 10:35:07 GMT

Richard,

must be done with native sql

Cheers

carsten

Example

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

topic SQL where clause case intensitive criteria in Application Development and Automation Discussions

SQL where clause case intensitive criteria

Re: SQL where clause case intensitive criteria