Code Snippet Series: Case Insensitive Table Query

[amy_king]

This post is part of a series on code snippets. The complete list of posts in the series is available in the document Code Snippets: A Blog Series.

The ability to query case-sensitive data in a case-insensitive way is a frequent request from both business owners user groups. Examples include searching for a surname "McDonough" or an equipment model "F123b-A". Users may not know the correct capitalization and a caps-only field may not be available in the database table.

Case-insensitive comparison is possible in Open SQL with the release of SAP_BASIS 751. On older releases, we can use native SQL to perform a case-insensitive query of any database table.

  TRY.

      EXEC SQL.

        SELECT equnr

               FROM equi

               INTO :the_equnr

               WHERE UPPER(herst) = :an_uppercase_herst

               AND   UPPER(serge) = :an_uppercase_serge

               AND   UPPER(typbz) = :an_uppercase_typbz

      ENDEXEC.

    CATCH cx_sy_native_sql_error .

  ENDTRY.