Data flows

Question

I have a requirement that in my source in some of the fields of string type, we have string starting and ending with single quotes (') for example like 'abcdefgh'. In mapping dataflows, we need to check that the string is starting and ending with single quotes, if yes, then both the leading and trailing single quotes must be removed.

How can this be achieved in mapping dataflows. Any help is appreciated!

Answer 1

Hello @Max ,

Thanks for the question and using MS Q&A platform.

There is a similar question asked by different user here, I'm not sure if it was you or someone else but sharing the solution here again. Similar thread link: https://learn.microsoft.com/en-us/answers/questions/1024486/adf-mapping-data-flows.html

You can use trim() function in your expression of a derived column transformation to remove leading and trailing characters.

Use this expression and it should help remove trailing and leading characters

trim(sourceColumnName, '\'')  

Here is a sample:

In case if you have single quotes or a special character anywhere in the give string then you can use replace() function. Please see example below:

replace(souceColumnName, '\'', '')  

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how