Wildmatch lookup against table

[#1]

Hi All

I need help trying to extract a value out of a freetext field,

The highlighted part are the bits i want to extract.

data example

AR  Credit Notes - I0049

AR Credit  Notes - I0049

adjustments

accruals

accruals

A0282S

A0132P promo  write off

A0120P

A/R Invoices  - P0004

A/R Invoices  - P0004

Is there a way to do a wildmatch lookup against a table of account numbers and extract the matching values.

[#2]

I believe you can extract the fields you want by using:

where wildmatch(trim(fieldname), '*10049','A0282S*','A0132P*','A0120P*','*P0004')

Keep in mind * is the same as % in SQL.

Then in order to get just those parts from each field you would probably have to use some kind of conditionals:

Or you could leave off the where clause above and just get the parts you want below.

IF ( wildmatch(trim(fieldname),'*10049','*P0004'), right(trim(fieldname),5),

IF ( wildmatch(trim(fieldname),'A0282S*','A0132P*','A0120P*'), left(trim(fieldname),6) ) ) as newField

newField would then contain the part you hilighted above, fieldname is the freetext field.

I added in trim assuming that the data could have extra spaces, but you could omit it if that is not an issue.

Hope this helps!