3 same REGEX queries with R script and 3 different results.

[Ezay]

Hello, this is my first topic here, i'm glad to share with you my "REGEX with R" issues.
This was also my first calculated column with a R script but I really needed this language to test data against REGEX.

This is my script : 

# 'dataset' contient les données d'entrée pour ce script

Regex_apply <- function(x,y) {  grepl(x , y)}
output <- within(dataset,{ValidRegex=Regex_apply(dataset$REGEX_simple, dataset$VAT )})

 It's build to apply the REGEX value contained in the REGEX_simple column to the VAT code in the VAT column and to return a boolean TRUE/FALSE.
I'm working on a table with companies in around 30 different countries and i expect to check if their VAT code is OK.
My script works well when i reduce the scope with only one country (example : Germany) and find the good and the bad ones.
When i add a second country in my scope, the results are fine for 1 country and totally wrong for the other country 
When i go for the full scope, the results are either OK or completely false without my being able to detect a pattern on what separates why one country is OK and another not.

( i had to hide some data but there were 9 numbers after DE and 12 after the SE, the result must return true for each row, in each array).
But we have : 

numbers of country	DE	SE
1	OK	OK
2	OK	KO
full	KO	OK

I think that i missed something and i will be really happy if someone could help me find what it is.

[Icey]

Hi @Ezay ,

This issue is caused by the argument 'pattern' can only use the first element. 

Reference: grep: Pattern Matching and Replacement (rdrr.io)

Then I refer to this post to get a workaround:

# 'dataset' holds the input data for this script
Regex_apply <- function(x,y){
if(grepl(x, y)){
  check <- TRUE
} else{
  check <- FALSE
}
check
}
library(purrr)
output <- within(dataset,{ValidRegex=map2(dataset$REGEX_simple,dataset$VAT,Regex_apply)})

However, this won't work in Power BI. 

Please give me some time to research. Once there is a solution, I will post it as soon as possible.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.