CODEWITHSUNDEEP
sqlexpressionazure-data-factorygoogle-cloud-dataflowquote
Manish Jain
Manish Jain

Reputation: 237

ADF data flow concat expression with single quote

I need to generate a SQL string using Azure data flow expression builder, but it won't allow me to add a single quote between my string using Concat function

I need to have a SQL string as below

SELECT * FROM ABC WHERE myDate <= '2019-10-10'

Here 2019-10-10 is coming from a parameter, and so expression which I build is like below

concat('SELECT * FROM ABC WHERE myDate >=','''',$ToDate,'''')

but above statement unable to parse expression.

The result will be executed as a SQL query. SQL query doesn't allow double quote. It has to be single quote.

This is easily achievable using data factory expression but not with azure data flow expression.

Upvotes: 5

Views: 26121

Answers (5)

Mitul Panchal
Mitul Panchal

Reputation: 632

You can specify by adding 4 comma at both side to parameter. This will applicable when you passing SQL query and the parameter is in String format.

@concat('SELECT * FROM DW_DEV.Schema_Name.DEMO_CONFIG WHERE SOURCESYSTEM = ','''',pipeline().parameters.SourceSystem,'''')

Upvotes: 2

blobbles
blobbles

Reputation: 272

I also had a lot of trouble with this, trying to pass 2 timestamp variables with quotes around them. Lots of trail and error and I was able to use string interpolation to get it going. Expression ended up being:

"SELECT c.data.user_id ... FROM c WHERE c.data.date > '{toString($StartTime)}' AND c.data.date <= '{toString($EndTime)}'"

Upvotes: 0

jyoti
jyoti

Reputation: 31

After doing a lot of trials and experiments, I got a solution for this,Use ''(one extra quote) instead of '. it will work. for example.

@concat('Select batchid from execution_Log where Execution_status=''Success'' and Hierachy_ID = ', pipeline().parameters.Hierachy_ID)

Upvotes: 2

Leon Yue
Leon Yue

Reputation: 16431

I called Azure Support and they told me a using bellow expression can help us add single quote to the Column or data :

toString('\''+ toString(byName('col2'))+ '\'')

So your expression should be:

concat('SELECT * FROM ABC WHERE myDate >=',toString('\''+ toString(byName('$ToDate'))+ '\'') )

I tested in my date type column2 and it works well. enter image description here

Hope this helps.

Upvotes: 2

Mark Kromer MSFT
Mark Kromer MSFT

Reputation: 3838

Today, you can do it this way:

'select * from saleslt.product where myDate >= \'' + $ToDate + '\''

Essentially, just use + with escaped single quotes.

We're releasing a new string interpolation feature next week to make this much easier. Any expression, column, or parameter can be used inside double-quotes.

Your example will look like this:

"SELECT * FROM ABC WHERE myDate >='{$ToDate}'"

Much easier. I'll send out an announcement once the feature is ready next week.

Upvotes: 2

Related Questions