«

[skb3]

Could anyone help me here I have products in a table, but I need to select a distinct list, the list contains the following:

Contract Purchase Advance Advance
Hire Purchase Advance Advance
Hire Purchase Arrears
Finance Lease Advance

In the case of the first couple of lines I need to replace only 1 instance of 'Advance' not both. Is there anyway I could do this, I know the Replace() function does not replace only one instance, i.e.

Select Replace(ProductNames, 'Advance', '') from ProductList

Would removed both instances, could anyone help me please?

Thanks in Advance,

Steve

[urstop]

Is the repeated word in this case "Advance" always repeated word after word or it could be anywhere in the data?

If the repeatition is always word after word you can just modify your statement a bit like below

Select Replace(ProductNames, 'Advance Advance', 'Advance') from ProductList


Regards,
UrStop