ABAP HANA 7.51: Case Insensitive search with SELECT
Case Insensitive search can be achieved by using a combination of either of two:
- String function TO_LOWER( ) and SQL string function LOWER( ) with LIKE operator
- String function TO_UPPER( ) and SQL string function UPPER( ) with LIKE operator
Sample Source Code: By converting Search string to lower case
” Lower case
DATA(search_term) = `Box`.
search_term = |%{ to_lower( search_term ) }%|.
SELECT
DISTINCT maktx FROM makt
WHERE lower( maktx ) LIKE @search_term
INTO TABLE @DATA(lt_search_result).
IF sy–subrc IS INITIAL.
cl_demo_output=>display( lt_search_result ).
ELSE.
WRITE : ‘Search failed!’.
ENDIF.
In above code, the search term is first converted to lower case which precedes and trails with a %(works as a wildcard to be used with LIKE operator). A SELECT query is made on MAKT which contains Material descriptions(may contain mixed lower and upper cases).
If someone makes a query to find Material description checking equality with a string, there is a high chance to miss the right hit.
To get rid of this situation, query converts material description to lower case inside query and then compares it with search term using LIKE operator. This technique offers a good chance for the right hit.
Output: By converting Search string to lower case
The output gives all Material descriptions which contain ‘Box’ in any part of the string with any case.
Sample Source Code: By converting Search string to upper case
” Upper case
DATA(search_term) = `Box`.
search_term = |%{ to_upper( search_term ) }%|.
SELECT
DISTINCTmaktx FROM makt
WHERE upper( maktx ) LIKE @search_term
INTO TABLE @DATA(lt_search_result).
IF sy–subrc IS INITIAL.
cl_demo_output=>display( lt_search_result ).
ELSE.
WRITE : ‘Search failed!’.
ENDIF.
This a very similar code with only two differences:
- Search term string is converted to UPPER case using to_upper string function
- SELECT query uses UPPER string function to convert Material description to upper case
All the functionalities work same!
Output: By converting Search string to upper case
The output gives all Material descriptions which contain ‘Box’ in any part of the string with any case. This is exactly same to the first Variant of case-insensitive search discussed above.
how do we use the same in select options
You will need to prepare data before passing to SELECT statement.