If Contains In-DB Formula

[nsandhu]

Hi Alteryx Experts,

I am trying to do some text parsing using IN-DB tool. Where I need to find out a word in a line like

CASE
WHEN contains (`text_line`, "ABC") THEN "ABC"

ELSE "Unknown"
END

But it is ending up in an error message like Syntax error and No IN-DB Database Data Received.

Please suggest.

[jrgo · Accepted solution]

Hi @nsandhu 

This really would depend on the DB you're connecting to as CONTAINS may not be a valid function.

You could try:

CASE
WHEN `text_line` LIKE "%ABC%" THEN "ABC"

ELSE "Unknown"
END

If that doesn't work, use an Filter In-DB tool and see how it would generate the expression.

Edit: added wildcard to the like expression. Also, (depending on the DB) this may also be case sensitive.

Hope this helps!

Jimmy
Teknion Data Solutions

[echuong1]

Ensure you're using the correct field name syntax in the SQL query - I'd recommend inserting the fields using the built in drop down. 

Also, if you use the "like" function, ensure you include the wildcard % character to look for all values containing the specified string. 

Hope this helps!