Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I need some assistance to find and use a sql server algorithm for categorising my list of products

Posted on 2013-05-10
7
Medium Priority
?
327 Views
Last Modified: 2013-05-27
Hi,

I have a table in my sql server database that contains the following columns...

ID
Name
CommonName
Description

Now, I need to add another column called, 'Categorisation' and this column will be used to filter out the different types of product.

Now my idea of doing this was to run some kind of algorithm over the table that will do some clever pattern matching for me over the 3 columns Name,Common Name and Description. I know it won't be 100% accurate but there are 1300 products and I was hoping to at least get some of these products categorised automatically categorised.

Using algorithms to analyse data is a complete new experience for me so a thorough explanation would be greatly appreciated. Thanks.
0
Comment
Question by:jazz__man
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 39155311
You can use LIKE '%prodcut%' in your where clause.

For example:

SELECT * FROM table where Name LIKE '%product%'
OR CommonNam  LIKE '%product%'
OR Description LIKE '%product%'

This may take a LONG time depending on how much data is in your db, so you can do them separately as well

SELECT * FROM table where Name LIKE '%product%'


SELECT * FROM table where CommonNam  LIKE '%product%'


SELECT * FROM table where  Description LIKE '%product%'

the product you're looking for would go between the '% and the  %'
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39155315
Something like:
update tbl
set Categorisation = case
  when Name like '%Something%' and CommonName like '%Something%' and description like '%Something%' then 'Something'
  when Name ...
end

Open in new window

You can also write individual update commands for each pattern combination, and put the condition into the where clause. It is very similar to above.
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39155343
Qlemo,

Thanks very much, this is very helpful but I would have to write hundreds of case conditions to run this. This is why I was looking for something a bit more mathematical like an algorithm to do a bit of pattern matching for me. There are 900 distinct products in a database of 1300. Now if there were 100 distinct records in 1300 then life would be much easier but im not in this position. Hope this makes sense. Thanks
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 71

Expert Comment

by:Qlemo
ID: 39155367
To develop something smart we would have to know the details. SQL cannot guess, so you have to tell it something concrete.
You could e.g. build it stepwise:
update tbl set Categorisation = 'Something' where name like '\[A-F\]%' escape '\' and Categorisation is null;
update tbl set Categorisation = 'Somethong' where CommonName like '_\[0-9]%' escape '\' and Categorisation is null;

Open in new window

and so on. That will only update rows not already categorized. You should go for the most common categories first, and then treat the more special cases.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39155609
>>Now, I need to add another column called, 'Categorisation'

Don't you need another TABLE? e.g.
ProductCategory
ID, Product_ID (FK), Category

so for one row in the existing table you may have one or more rows in the new table

then your queries would be far easier and more efficient

select
*
from products p
inner join ProductCategory pc on p.id = pc.product_id
where pc.category ='fish'
or pc.category = 'chips'


+ You could take this a step further and have a table of Categories, so that only allowed categories can be added to a product.

sorry, but I just hate the idea of you evaluating strings (like '%something%') at each and every select statement - it will be a nightmare.

Normalization makes life easier in the long run.

If you go down this route, what you will need to generate would be a set of insert statements to the new table, and this could leverage the table of categories.

let's say you have a table of Categories like this

Fish
Chips
Salt
Vinegar

then you could do something along these lines (to generate the insert data, not as a daily activity)

select
p.*
c.category
from products p
inner join categories p.description like ('%' + c.category + '%')
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39155668
PortletPaul,

You are making me hungry!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39155731
LOL, maybe that's what's on my mind... [note to self, eat]
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question