CHARINDEX("(",ColumnName)-1) errors

Question

I'm trying to clean a column data by deleting what comes after the open parentheses:

LEFT(columnName, CHARINDEX("(",columnName)-1) as columnName

But I keep getting this error:

An error occurred while checking the query syntax. Errors: An unmatched parenthesis occurs in the query.

Answer 1

I get the feeling, its SFMC Query validator being 'helpful' and reading your string (which by the way, you should be using (') single quotes, not (") double quotes) as if it were a parenthesis to be checked. Which is very annoying.

What you can do to get around these overzealous validations is use the CHAR() funtion to have the SQL fill in that char without explicitly writing it. E.g. ( = CHAR(40).

So for instance:

SELECT SubscriberKey,
LEFT(columnName, CHARINDEX(CHAR(40),columnName)-1) as columnName
FROM [test_charIndex]

Should do the trick. For example on my test data set of:

SubscriberKey  |  columnName
8675309        |  my name (is cool)

It returned:

SubscriberKey  |  columnName
8675309        |  my name