Skip to Content
Technical Articles
Author's profile photo Johan Wigert

Using native SQL for case-insensitive search

As I was developing an OData service related to contact persons in SAP CRM, I was faced with the scenario that the user wanted to search based on e-mail addresses of the contact persons. The e-mail addresses are stored in the database with a mixture of uppercase and lowercase letters. In this post, I’ll explain how the requirement can be met by using native SQL.

Scenario

The e-mail addresses of the contact persons are stored in table ADR6. The field where the e-mail address is stored is SMTP_ADDR, which has a data element with a case-sensitive domain handling 241 characters. Since the field is case-sensitive, we have e-mail addresses with a mixture of uppercase and lowercase letters, e.g.:

  • TestUser1@somedomain.com
  • testUSER2@somedomain.com
  • testuser3@SomeDomain.com

In order to search irrespective of the casing, we want the e-mail addresses to be uppercase only when performing the search.

Search field SMTP_SRCH

There is a field SMTP_SRCH in table ADR6 which is provided to simplify the search by storing the e-mail address uppercase. However, there is a serious limitation to this solution. The field is only 20 characters long. Since many e-mail addresses are longer than 20 characters, the field isn’t very useful.

Native SQL when selecting from the database table

With the help of native SQL we can transform SMTP_ADDR ot uppercase in the SELECT statement when selecting directly from the database table ADR6:

DATA user_email TYPE ad_smtpadr.

TRY.

    " Note that table and field names must be uppercase
    EXEC SQL.
      SELECT
          SMTP_ADDR
          INTO :user_email
          FROM ADR6
          WHERE CLIENT = :sy-mandt
            AND UPPER(SMTP_ADDR) = 'Q.W@R.S'
    ENDEXEC.

  CATCH cx_sy_native_sql_error INTO DATA(native_sql_excecption).
    DATA(error_text) = |{ native_sql_excecption->get_text( ) } SQL CODE: { native_sql_excecption->sqlcode } MSG: { native_sql_excecption->sqlmsg }|.
    MESSAGE error_text TYPE 'E'.
ENDTRY.
WRITE: / user_email.

So if the e-mail address in database table ADR6 is lowercase q.w@r.se, the entry is still found.

Native SQL when selecting from the DDL SQL View

Native SQL can also be used when selecting from the DDL SQL View of the CDS containing table ADR6:

DATA user_email TYPE ad_smtpadr.

TRY.

    " Using the DDL SQL View of the CDS works
    EXEC SQL.
      SELECT
          EMAIL
          INTO :user_email
          FROM ZCDSCPEMAIL
          WHERE MANDT = :sy-mandt
            AND UPPER(EMAIL) = 'Q.W@R.S'
    ENDEXEC.

  CATCH cx_sy_native_sql_error INTO DATA(native_sql_excecption).
    DATA(error_text) = |{ native_sql_excecption->get_text( ) } SQL CODE: { native_sql_excecption->sqlcode } MSG: { native_sql_excecption->sqlmsg }|.
    MESSAGE error_text TYPE 'E'.
ENDTRY.
WRITE: / user_email.

Native SQL when selecting from the CDS

Native SQL can not be used when selecting from the CDS containing table ADR6:

DATA user_email TYPE ad_smtpadr.

TRY.

 " Using the CDS name does not work
    EXEC SQL.
      SELECT
          EMAIL
          INTO :user_email
          FROM ZCDS_CONTACT_EMAIL
          WHERE MANDT = :sy-mandt
            AND UPPER(EMAIL) = 'Q.W@R.S'
    ENDEXEC.

  CATCH cx_sy_native_sql_error INTO DATA(native_sql_excecption).
    DATA(error_text) = |{ native_sql_excecption->get_text( ) } SQL CODE: { native_sql_excecption->sqlcode } MSG: { native_sql_excecption->sqlmsg }|.
    MESSAGE error_text TYPE 'E'.
ENDTRY.
WRITE: / user_email.

The code above results in the following error: You tried to work with the name of a table or view that does not exist in the database SQL CODE: 208 MSG: Invalid object name 'ZCDS_CONTACT_EMAIL'.

Conclusion

Even though it would have been possible to meet the requirement by using native SQL, we decided to implement the solution outlined in Note 1664239 instead. We opted for this solution since it is more general, and we avoid the dependency to a specific database implied by native SQL. I still think it was educational to explore the native SQL route.

This blog post first appeared on the Developer Voyage blog at https://www.developervoyage.com/2021/02/12/using-native-sql-for-case-insensitive-search.html

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Bence Bodzsar
      Bence Bodzsar

      There is UPPER in Open SQL, why you need native?

      https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abensql_function_upper_abexa.htm

      Author's profile photo Matthew Billingham
      Matthew Billingham

      Not all of us are on 752!

      Author's profile photo Johan Wigert
      Johan Wigert
      Blog Post Author

      I'm on 750, where UPPER is not supported in Open SQL. I should have mentioned that in the post. Thanks for commenting.

      Author's profile photo Matthew Billingham
      Matthew Billingham

      Generally, I find ADBC (CL_SQL_STATEMENT etc.) better than EXEC.

      Author's profile photo Oleg Bashkatov
      Oleg Bashkatov

      In emails I think more prefered way is to search with Similarity Search.

      There are options both for Oracle (as Matthew mentioned via ADBC) and HANA (AMDP).

      The sample could be found here

      https://github.com/OlegBash599/ZC8A_001