AzureSynapse - Replace apostrophe in raw data

Question

I have some raw JSON data which I need to insert into my SQL database. I am able to read the data using a Lookup activity but am facing difficulties with the SQL insert as some of the fields in the JSON contain apostrophes ('). In python, I am able to easily account for this using re.sub("'", "''", text) but I cannot seem to find a way to do this in my AzureSynapse pipeline. Azure does not allow me to use double quotes (") in the replace() function so I am unable to do something like replace(text, "'", "''").

Is there a way to catch and replace single quotes with two single quotes in the AzureSynapse pipeline? Maybe an escape character in the replace function that allows me to use the single quote without closing the string?

Many thanks for any help!

score 0 · Accepted Answer · answered Sep 09 '22 at 14:12

0

I reproduced the same in my environment with single quotes and created a sample set variable.

enter image description here

Created two parameters with the name one and two added dynamic parameter

enter image description here

added dynamic parameter as@replace(variables('var'),pipeline().parameters.one,pipeline().parameters.two)

enter image description here

Now you can check single quote changes to double quotes.

enter image description here

answered Sep 09 '22 at 14:12

B. B. Naga Sai Vamsi

2,386
2
3
11

Thank you Bhanunagasai. Variables were indeed the answer. Apologies for taking so long to mark this as a solution. – Waleed Alfaris Sep 27 '22 at 06:38

AzureSynapse - Replace apostrophe in raw data

1 Answers1