Case Insensitive search with SELECT

Index

 

ABAP HANA 7.51: Case Insensitive search with SELECT

Case Insensitive search can be achieved by using a combination of either of two:

  1. String function TO_LOWER( ) and SQL string function LOWER( ) with LIKE operator
  2. 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 sysubrc 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
DISTINCT
maktx FROM makt

 WHERE upper( maktx ) LIKE @search_term

 INTO TABLE @DATA(lt_search_result).

 

IF sysubrc IS INITIAL.

  cl_demo_output=>display( lt_search_result ).

ELSE.

  WRITE : ‘Search failed!’.

ENDIF.


This a very similar code with only two differences:

  1. Search term string is converted to UPPER case using to_upper string function
  2. 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.

Index

 

2 thoughts on “Case Insensitive search with SELECT

Leave a Reply