I have a scenario where I have to merge products and calculate the quantity.
For example: Product name is "Gadsden35", this text can be occurred in first, middle or in last of product name as well as name can be in upper or lower case such as illustrated in listed below table.
Now, I have to merge them and calculate the Quantity such as Gadsden35 quantity = 7
Please note, product names vary in my data source file. So, Gadsden35 is not always remain the same as the selection vary the product names vary.
I had a similar problem in one project of mine...
If you know the list of these names you want to show, you can match them in the script.
if(Capitalize(Product) like '*GADSDEN35*', 'Gadsden35',
if(Capitalize(Product) like '*ADF-JPS-001*' ,'ADF-JPS-001', ........)) as ProductNew.
In my case, there were few values to be matched... if you have lots of them, maybe it can be difficult
My product names are different from starting to end but if there is any one word match to each other then it must be add.
Such as given above example input and output.
Maybe I'm wrong but if you are selling/buying those products you must have defined some unique identification for them.
Otherwise this business is kind of "unique" one...;)
Can you dig deeper in your database?
Maybe you will find something that would help here.