How to check if a parameter in my dataflow is numeric or not?

Question

Hi,

I have a mapping data flow in ADF that takes in a parameter, $Staging_Logical_Key[1].
This parameter can be an IBAN (International Bank Account Number) or a BBAN (Basic bank Account Number).
The IBAN is a string, 18 positions, like 'NL88AAAA3434565677'.
The BBAN is a bigint , length 10, like 3434565677.

The target table contains the alphanumeric IBAN as well as the numeric BBAN.
When the parameter contains the IBAN, I have to compute the BBAN. It is the last 10 positions of IBAN.
When the parameter contains the BBAN, IBAN in target table gets value 'XX'

For field IBAN this is the formula in Derived Column activity:
iif(substring(byName($Staging_Logical_Key[1]),1,2)=='NL' && substring(byName($Staging_Logical_Key[1]),5,4)=='RABO' ,iif(substring(byName($Staging_Logical_Key[1]),9,1)!='0', byName($Staging_Logical_Key[1]),'XX'),'XX')

For field BBAN this is the formula in Derived Column activity:
iif(substring(byName($Staging_Logical_Key[1]),1,2)=='NL' && substring(byName($Staging_Logical_Key[1]),5,4)=='RABO' ,iif(substring(byName($Staging_Logical_Key[1]),9,1)!='0', toLong(right(rtrim(byName($Staging_Logical_Key[1])),10)),toLong(-1)),toLong(-1))

When the parameter $Staging_Logical_Key[1] contains an alphanumeric IBAN both derived columns work.
When the parameter $Staging_Logical_Key[1] contains a numeric BBAN it fails. Doing a substring on a numeric fails.

So, after this long story, the main questions are:

1. How do I check if a field / parameter is numeric ?
2. How do I check if a field / parameter is alphanumeric (containing more than only numbers)?

Hope someone can help. Read about regexmatch, but don't understand much about regex.

Regards
Ron

Answer 1

Hi @Poel van der, RE (Ron) ,

You can try isLong() function in the DataFlow. Please see the screenshot below. Hope this helps! Thanks!

Answer 2

Hi

somebody told this would also work

iif(regexMatch(toString(byName($Staging_Logical_Key[1])),'^[0-9]*$'),toLong(byName($Staging_Logical_Key[1]))

just as tip