Search code examples
azuresharepointunicodeazure-sql-databaseazure-logic-apps

How to hanndle Unicode Character (U+200E) in azure logic app

I have a problem with recognizing and converting Unicode Character (U+200E).

A brief description of the flow:

  • I take the list from sharepoint

  • I take the list from sql

  • I instantiate a variable

  • I take the values from SQL and store them for the conditions to be checked in the variable.

    enter image description here

  • I check if the item from sharepoint list (Get Items) is already in sql (our previously initialized variable)

code of condition: contains(variables('IMO'),int(items('For_each')?['IMO']))is equal to true

enter image description here

Result:enter image description here

enter image description hereIn general, for 99% of records this code works because there are no invisible characters. I wondered for a long time why this one record does not work in condition. When I exported this column I noticed that one record looks like this:

enter image description here

The field on sharepoint from which we take data is a text field. It contains numeric values but that is not important.

In sharepoint list, this record looks normal.

enter image description here

In get items output too:

enter image description here

And my question, is there an easy way to remove the unicode character so that you can fully use the extracted value without worries?

I'm trying to get around the removal of special characters somehow, so that in the future there will be no such errors. I expect someone has a proven method on how to do this.

Solution

  • After reproducing from my end, it was working fine. However, one way to solve this is to use replace function.

    replace(items('For_each')?['sample']['<Path_To_Your_Value>'],'[U+200E]','')

    or

    replace(items('For_each')?['sample']['<Path_To_Your_Value>'],'\u200E','')

    enter image description here

    enter image description here

    Another way you can try is, instead of converting the value to int, you can iterate through SQL list, in each iteration convert the current item to string and then use contains.