check for pipe characters in a field and use if condition on this field

Question

In the dataflow, I am checking for data in a field that could have Pipe (|) characters or alphanumeric characters with Pipe(|) between them.

If there is only pipe (|)characters, then use iif condition and mark it as Y .

If there are alphanumeric characters with Pipe(|) between them or only NULLS or only alpha numeric, then mark as N

field1	flag_field
		Y
AB	CD	N
AB	N
NULL	N
					Y
			Y

Can we use regexMatch and provide the code please

Answer 1

Hi @Peruka, Sikander reddy

Thank you for posting your query!

To achieve the desired output, you can use a combination of regular expressions and conditional logic to evaluate each field. In Azure Synapse Analytics or Azure Data Factory, you can use a Data Flow with a derived column transformation to apply this logic.

Here's how you can implement this using a derived column transformation with a regex match:

Create a Derived Column Transformation - Add a derived column transformation to your data flow.

Define the Condition - Use an expression to evaluate whether the field contains only pipe characters or if it includes alphanumeric characters.

You can use the iif function combined with regexMatch to create the desired condition. The regular expression will check if the field contains only pipe characters.

iif(isNull(field1) || regexMatch(field1, '^\|+$'), 'Y', 'N')

Explanation:

• isNull(field1): Checks if the field is NULL.
• regexMatch(field1, '^\|+$'): This regular expression matches strings that consist entirely of pipe characters. The ^ and $ denote the start and end of the string, respectively, and \|+ matches one or more pipe characters.
• The iif function returns 'Y' if the field is either NULL or matches the regex for only pipe characters, and 'N' otherwise.

Apply the Derived Column - Use this expression in the derived column transformation to generate a new field (flag_field) with the desired output.

By following these steps, you should be able to mark fields with only pipes as 'Y' and all other conditions (alphanumeric with pipes, only alphanumeric, or NULL) as 'N'. For more details refer: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions

https://learn.microsoft.com/en-gb/azure/data-factory/control-flow-expression-language-functions

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

1. | --> output to a new field as Y
2. AB|CD -> output to a new field as N
3. AB -> output to a new field as N
4. NULL -> output to a new field as N
5. ||||| -> output to a new field as Y
6. || -> output to a new field as Y

Note: In the original questions, in the table it is missing pipe characters in the field1.

So, I have entered the data and the required output to make the question clear.