Skip to Content

How to write HANA SQL to find records with invalid characters

Hello Experts,

Please let me know how to perform a SQL query to find out invalid characters in a table column.

SELECT * FROM TABLE WHERE FIELD LIKE '%[^A-Za-z0-9]%'

Something like above code will work in normal SQL engines but not in HANA.

Are there any alternatives for [] defining range while doing the pattern search.

And if any alternatives works for NCLOB data type columns.

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Mar 30, 2017 at 06:52 AM

    No, something like this wouldn't work with "normal" SQL engines, because the LIKE operator does not support regular expressions in the SQL standard.

    What you can do, however, is to use the HANA SQL documentation and check for the LIKE_REGEXPR() function ( http://help-legacy.sap.com/saphelp_hanaplatform/helpdata/en/b4/0d483dd34d47aa9cc89b4d8a6e617e/content.htm )

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 30, 2017 at 07:42 AM

    Thanks Lars. That should work :)

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 30, 2017 at 08:01 AM
    Add comment
    10|10000 characters needed characters exceeded