Partial Text String Manipulation

  • Thread starter Thread starter Gdub
  • Start date Start date
G

Gdub

Situation: Similar Product Names are imported in
scattershot fashion from an outside source into the
Product Field of the Product Table, i.e., "Vitamin C
Formula" or "Vitamin C Formula -- Wholesale" or "Vitamin C
Formula- Wholesale"

Desired Result: To have an update query that will take
any line items containing the "Vitamin C Formula
Wholesale" phrase regardless of number of random spaces
and dashes in front of "Wholesale" and reduce it to only
the root, "Vitamin C Formula"

Thank You
 
UPDATE TableName
SET FieldName = "Vitamin C Formula"
WHERE InStr([FieldName], "Vitamin C Formula") > 0
AND InStr([FIeldName], "Wholesale") > 0;
 
Situation: Similar Product Names are imported in
scattershot fashion from an outside source into the
Product Field of the Product Table, i.e., "Vitamin C
Formula" or "Vitamin C Formula -- Wholesale" or "Vitamin C
Formula- Wholesale"

Desired Result: To have an update query that will take
any line items containing the "Vitamin C Formula
Wholesale" phrase regardless of number of random spaces
and dashes in front of "Wholesale" and reduce it to only
the root, "Vitamin C Formula"

Thank You
Click to expand...

If you have A2002 or later, try running an Update query updating
[Product Name] to

Replace([Product Name], "-", " ")

and then another updating to

Replace([Product Name], " ", " ")

The latter query my need to be run a couple of times.
 
Gdub said:
Situation: Similar Product Names are imported in
scattershot fashion from an outside source into the
Product Field of the Product Table, i.e., "Vitamin C
Formula" or "Vitamin C Formula -- Wholesale" or "Vitamin C
Formula- Wholesale"

Desired Result: To have an update query that will take
any line items containing the "Vitamin C Formula
Wholesale" phrase regardless of number of random spaces
and dashes in front of "Wholesale" and reduce it to only
the root, "Vitamin C Formula"

Thank You
Click to expand...

This will come close, but having done this sort of thing in the past
myself, I can tell you it will not be perfect. It can be pretty slow, too.

UPDATE ProductTable
SET ProductName = "Vitamin C Forumla"
WHERE ((ProductName LIKE "*Vitamin*")
AND (ProductName LIKE "*C*")
AND (ProductName LIKE "*Formula*")
AND (ProductName LIKE "*Wholesale*))

This will catch instances of "Wholesale Formula,Vitamin-C", and other
crazy combinations.

However, it will also catch instances of "Vitamin C & D
Formula-Wholesale", which you probably don't want to change, since it seems
to be a completely different product. You could theoretically add an
exclusion for "D", but the problem is there can be so many combinations of
things (just in vitamins and minerals and health additives alone) that your
WHERE clause will wind up looking like a Christmas tree, assuming that all
possible combinations can be known in advance (usually they can't).


Good luck.
 
ONE more variation.

UPDATE ProductTable
SET ProductName = "Vitamin C Forumla"
WHERE ProductName LIKE "*Vitamin* C * Formula *Wholesale*"
 
Back
Top