How to write HANA SQL to find records with invalid characters

[former_member1100794]

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

[lbreddemann · Accepted solution]

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/conten... )

[k_gorin]

I would suggest you look into this https://blogs.sap.com/2014/04/08/text-analysis-with-sap-hana/

[former_member1100794]

Thanks Lars. That should work 🙂