question

AdityaRaj-8613 avatar image
0 Votes"
AdityaRaj-8613 asked ShaikMaheer-MSFT commented

Filter with quotes as string

I have a filter condition in which it is given as string e.g, if columnName is firstname then filter condition is firstname = 'xyz'. I am trying to add this filter in source query in data flow.
I am reading this filter condition from table where filtercondition is defined as text column
I am getting parsing error


{"StatusCode":"DF-Executor-ParseError","Message":"Job failed due to reason: Expression cannot be parsed","Details":""}


106647-image.png


106633-image.png





azure-data-factory
image.png (35.5 KiB)
image.png (95.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @AdityaRaj-8613 ,

Thank you for response. You need to handle adding \ symbols before single quote in your filter value using expressions. Please check detailed expression to do the same.

Step1: Create a below two variables.
FilterColVal - will hold your data from table
ModifiedFilter - to hold modified filter contidion with \ symbols in it to escape single quote
107445-variables.gif

Step2: Use set variable activity to store value with modified filter data(with \ symbol in it to escape single quote) using expressions
Expression used:

 @concat(
    substring(
      variables('FilterColVal'),0,indexof(variables('FilterColVal'),'''')
    ),
    '\',
    substring(
      variables('FilterColVal'),
      indexof(variables('FilterColVal'),''''),
      sub(
        lastindexof(variables('FilterColVal'),''''),indexof(variables('FilterColVal'),'''')
      )
    ),
    '\',
    substring(
      variables('FilterColVal'),lastindexof(variables('FilterColVal'),''''),1
    )
  )

107514-expression.png

Hope this helps. Thank you


  • Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

  • Want a reminder to come back and check responses? Here is how to subscribe to a email-notifications.


variables.gif (214.4 KiB)
expression.png (214.9 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It is working but it is failing in condition when filter = '1=1' . can you tell me how to handle this?

Because in db it is giving me error : SQL compilation error: Invalid data type [VARCHAR(3)] for predicate ['1=1']

0 Votes 0 ·

Hi @AdityaRaj,

Glad to know that above solution working fine for you.

From your original query, It seems '1=1',will be passed in to SQL query WHERE clause right? Correct me if I am wrong.
If its true, that '1=1' will be going to WHERE clause then please try to use == there. That mean '1==1'.

Thank you


0 Votes 0 ·

these filter condition comes from tables only. All filter condition are passed in select query only either '1=1' or name = 'maheer' . So, when I am putting name = 'maheer' ,it is passing when I am putting 1=1 ,its failing in set variable stage only. In set variable I am using function, you have shared above. When I am putting '1=1' this then it gives arror on database side while slecting. But my filter condition can be something like this '1=1'
or name='maheer' or name='maheer' and lname='shaik'.

0 Votes 0 ·
Show more comments
ShaikMaheer-MSFT avatar image
1 Vote"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @AdityaRaj-8613 ,

Thank you for posting query in Microsoft Q&A Platform.

I found the resolution.

If we have single quotes in dynamic content then we should escape them with \ symbol.

If we have Filter condition(name = 'maheer') as dynamic then we should make sure to escape single quotes with \ symbol.

In below example, I am created two variables one is for TableName and another is for Filter. If you observer values in Filter I used \ symbol to escape single quote. Kindly check below detailed steps to understand

Step1: Created two variables to have my TableName and Filter condition. Please note I escaped single quotes here with \ symbol.
107084-variablescreation.gif
Step 2: Data Flow activity
107085-dataflowactivity.gif
Step 3: Parameters in Dataflow
107094-dataflowparameters.gif
Step 4: Source Transformation with Dynamic Query
107086-sorucetransformation.gif
Hope this helps you.


  • Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

  • Want a reminder to come back and check responses? Here is how to subscribe to a email-notifications


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


I tried with your way only but in my case as data is read from table column filter with datatype text so I am not able to attach / to escape quotes ,I have written same query that you have used in above example .example of filter that would be : FIRSTNAME='xyz'. I tried with your way only but in my case as data is read from table column filter with datatype text so I am not able to attach / to escape quotes. In my dataflow activity stage, filter is passed as given in above input data with single and double quotes and in source query ,I have written same query that you have used in above example. example of filter that would be : FIRSTNAME='xyz'
even tried ---- 'FIRSTNAME='xyz''

[1]: /answers/storage/attachments/107177-image.png

[2]: /answers/storage/attachments/107179-image.png

[3]: /answers/storage/attachments/107180-image.png

[4]: /answers/storage/attachments/107231-image.png

0 Votes 0 ·
image.png (54.3 KiB)
image.png (28.6 KiB)
image.png (42.6 KiB)
image.png (42.2 KiB)

Since comments has only 1000 characters limit. Posted as separate answer. Kindly check. Than you.

0 Votes 0 ·