Desktop productivity for business analysts and programmers

Multiple regex query

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Multiple regex query

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)


Accepted Solutions
Solution
‎10-27-2017 10:29 AM
PROC Star
Posts: 549

Re: Multiple regex query

[ Edited ]

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}/'.

 

 

View solution in original post


All Replies
PROC Star
Posts: 549

Re: Multiple regex query

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

Occasional Contributor
Posts: 16

Re: Multiple regex query

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

 

Thanks, Ian

Solution
‎10-27-2017 10:29 AM
PROC Star
Posts: 549

Re: Multiple regex query

[ Edited ]

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}/'.

 

 

Occasional Contributor
Posts: 16

Re: Multiple regex query

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.

PROC Star
Posts: 549

Re: Multiple regex query

| means or

Occasional Contributor
Posts: 16

Re: Multiple regex query

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 279 views
  • 0 likes
  • 2 in conversation