Search using regular expression over CLOB/NCLOB datatype

[Former Member]

Hi Team,

As you guys know regular expression is available in hana now, which is very powerful feature for search. Is there a way to search using regular expression over CLOB or NCLOB datatype? i know in Hana documentation it is mentioned that regular expression doesn't support search over CLOB or NCLOB, but is there a way to cast it or some other format where i can use it?

Your help is highly appreciated.

Thanks,

Akshay

[lbreddemann · Accepted solution]

If your use case is not tightly bound to using REGEX, you might want to look into the full text search features. Other than that, there is no (well performing) way to run REGEX against the contents of a CLOB column. Casting to strings can only work in the boundaries of what a VARCHAR data type can hold, which is less than what could be stored in the CLOB column.

[former_member182302]

If the use case demands using REGEX like search, One of the ways is to create the FULL Text Index using CGUL rules over the Clob column and then use CGUL rules (similar to REGEX ) to create a custom dictionary . Which you might have to take a decision keeping the maintenance of the index as well

Regards,

Krishna Tangudu

[former_member182302]

Just to add, I was trying to search over procedures table and I was able to query using LIKE_REGEXPR as shown below:

I tested this on the version 1.00.102.

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

I tried using regular expression over NCLOB datatype but am getting LOB data exceeds maximum string length error.

Thanks,

Akshay

[lbreddemann]

That works because of implicit type casting.

[lbreddemann]

And this error is also due to implicit (and failing) type casting. I mentioned the size limitation of string data type before...

[former_member182302]

Thanks Lars for clarifying

[former_member182302]

To add, Not sure about your use case . But do have a look on this document,

SAP HANA:  Using Custom dictionary with CG... | SCN

You could probably create a full text index like this and with the help of CGUL rules you can implement the Regex over the CLOB column data and then store only the data of interest in the index. You might also want to think about the maintenance while going for this approach.

Let me know on how you proceed and do share your use case if possible

Regards,

Krishna Tangudu