-1

I'm trying to run a SQL query in Automation Studio in order to perform some data cleansing activities for a name field (here called 'BB'), however, I am getting the following error thrown from MC (which I do not receive in SQL outside of MC):

"An error occurred while checking the query syntax. Errors: Incorrect syntax near 'BB'."

Does anyone have an idea what this could be?

The being used syntax in Automation Studio is as follows:

select 
"BB",
SUBSTRING("BB", position(' ' in "BB") + 1)
from TableName
Improve this question

1 Answer 1

Reset to default
1

The first thing you need to know is that the SQL running in MC is not MySQL, but a limited version of SQL Server.

This explains why you can't use the POSTITION() function, but need to use it's equivelent which for this SQL version is CHARINDEX(). The validation does not recognize the original function and that's why it's throwing this error:

"An error occurred while checking the query syntax. Errors: Incorrect syntax near 'BB'."

Next, the SUBSTRING() function in SQL Server works differently, as it requires a 3rd parameter specifying how many letters should be extracted.

Lastly, the purpose of SQL in Marketing Cloud is to work with data extensions and their columns, so if you are using functions like you do here, you are required to specify the name of the column to which the value should be written. Without that you will get another error like this:

An error occurred while checking the query syntax. Errors: No column name was specified for column 2 of 'Qry'.'

So in the end your code needs to look somewhat like this:

Select
    "BB",
    SUBSTRING("C_ID", CHARINDEX(' ', "C_ID") + 1, LEN("BB")) as FunctionOutputColumn  
From 
    TableName
Improve this answer

Not the answer you're looking for? Browse other questions tagged .