Parse Field SQL

Topic

New Reply

Hello,

I have a field (Description) in my query that returns an invoice description, for example:

Description
Supplies (INV-000001)
Bookjs (INV-000002)

I need to parse the following into a new field:

Invoice
INV-000001
INV-000002

Is there any easy way to accomplish this via SQL?

Reply | Quote

Replies

Try something like mid([Description], instr(1, [Description], “(” ) +1, 10) This assumes the Invoice number is always ten characters.

Reply | Quote

Try something like mid([Description], instr(1, [Description], “(” ) +1, 10) This assumes the Invoice number is always ten characters.

If the number of characters in the Invoice number part could change you can use:
Mid([Description],InStr(1,[Description],”(“)+1,Len([Description])-InStr(1,[Description],”(“)-1)

The last bit works out how many characters between the ( and the ).

Reply | Quote