Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!