remove leading unwanted character

arkiboys 9,701 Reputation points
2022-06-27T13:27:18.907+00:00

Hello,
in ADF dataflow, I am ingesting several columns...
One of them is called, compantRef which may or may not have leading or trailing spaces...
Using a derived column transformation, I remove any spaces before or after the companyRef column.

expression currently used --> ltrim(rtrim(companyRef))
Question:
some of the records may have values such as :

companyRef

----------

companyxyz
companyURT
company54RTR
: companyEE21
...

I would like to remove the colon if it is present infront of the companyRef and remove the space too.
How can I achieve this?

Thank you

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,206 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nasreen Akter 10,811 Reputation points
    2022-06-27T13:41:53.63+00:00

    Hi @arkiboys ,

    you can specify the characters in the second option of ltrim() function. you can try something like:

    ltrim(rtrim(companyRef), ': ')

    Thanks!

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Subashri Vasudevan 11,216 Reputation points
    2022-06-27T14:05:56.107+00:00

    Hi @arkiboys ,

    Thanks for the ask.

    You can use trim() instead of ltrim() and rtrim().

    For your scenario, you can try something like below as @Nasreen Akter mentioned.

    Trim(Trim(companyRef),':')

    Inner trim will remove leading and trailing spaces and outer trim will remove colon. Try out and let us know please.

    Thank you.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.