Multiple regex query

[ICL1986]

Hi, I'm working with some banking data containing IBANs, and would like to use a list of regular expressions (using SUBSTR and PRXMATCH) to extract them from text strings. I've tried creating a separate table containing the regex, for example ‘/AL\d{24}/’,
 '/AD\d{20}/’, ‘/AT\d{16}/’, '/LV\d{2}\D{4}\d{13}/', and then bringing that in as a column to query against the data, but am getting no results. Does anyone know how I might be able to do this? Current PRXMATCH expression is:

PRXMATCH(t2.Regex,t1.TextcolumnwithIBAN)

[kiranv_]

it will be easier to help, if you can provide an example/sample of what is not working and what your output should look like

[ICL1986]

Sure, I've attached a short representation of the two tables, and the desired output from the PRXMATCH statement.

Thanks, Ian

[kiranv_ · Accepted solution]

still it is not very clear to me what code you have tried. But if you are using multiple values you need to use a pipe not a comma.

comma

‘/AL\d{24}/’,'/AD\d{20}/’, ‘/AT\d{16}/’, '/LV\d{2}\D{4}\d{13}/'

 pipe

‘/AL\d{24}/’|'/AD\d{20}/’| ‘/AT\d{16}/’| '/LV\d{2}\D{4}\d{13}/'.

[ICL1986]

With the two tables I attached, I created a query within Table 1, added Table 2 to the query, and then added a column with the advanced expression PRXMATCH(Table 2.IBAN Regex, Table 1.Text Column). There are just under 70 IBAN types but I suppose using the | divider would work, and then I could extract countries from there.

[kiranv_]

| means or

[ICL1986]

This should work fine, I was being lazy and trying to get countries linked in through a table join