September 1, 2010 at 5:04 pm
Based on contents of a 'Description' field - Whats the best way?
They all have 'different' descriptions, but they have keywords, is there a quick way to find out what the most common words are?
Say the descriptions are for cars... a query may return the following 'breakdown' from all the records
22% contain '4-door'
3% contain 'all wheel drive'
19% contain 'ford'
I hope I make sense. If not let me know and I will explain further.
September 1, 2010 at 5:09 pm
shaun-744740 (9/1/2010)
Based on contents of a 'Description' field - Whats the best way?They all have 'different' descriptions, but they have keywords, is there a quick way to find out what the most common words are?
Say the descriptions are for cars... a query may return the following 'breakdown' from all the records
22% contain '4-door'
3% contain 'all wheel drive'
19% contain 'ford'
I hope I make sense. If not let me know and I will explain further.
Problem is clear, what doesn't really make much sense is the business requirement.
Having said that you can easily produce required output by counting rows where description like '%4-door%', etc.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 1, 2010 at 5:13 pm
Hey, thanks very much for the quick response!
Maybe this will explain things a little better-
The company initially had a list of 200 engineering components, easy to find in a simple select * list, now there are just over 3,000 and its very hard to navigate through, they have requested a 'breakdown/category' page with the most common 20 or so key-words for the components to quickly browse through the data. (They have a search they just dont seem keen on using it - crazy!!!)
I just need to figure out what the most common ones are
September 1, 2010 at 5:17 pm
shaun-744740 (9/1/2010)
Hey, thanks very much for the quick response!Maybe this will explain things a little better-
The company initially had a list of 200 engineering components, easy to find in a simple select * list, now there are just over 3,000 and its very hard to navigate through, they have requested a 'breakdown/category' page with the most common 20 or so key-words for the components to quickly browse through the data. (They have a search they just dont seem keen on using it - crazy!!!)
I just need to figure out what the most common ones are
:w00t: Crazy indeed but, believe me I've seen worse than that 😀
Wouldn't be easier to add a Category column and work with the customer to categorize those items?
You can suggest a basic list of categories by doing the word-ranking they are asking for, I insist, WHERE description LIKE '%something%' appears to be the primary tool for it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 1, 2010 at 5:22 pm
Eek!
They don't have the will or foresight to go through each record to add a category (it was a small reference system for one person that got rolled out to 5-user sales team ...) they are a bit of a nightmare client. They want results with 0 work.
I use the 'like %something%' for the search, its just for the categories i need to figure out what the 20-30 'somethings' are, and other than manually going through and going off estimation, i am a bit stuck in getting a good (simpler) solution
September 1, 2010 at 5:31 pm
In the description column, are the words you need delimited by something? If you have:
ford, 4-door, all wheel drive, etc....
you could parse the list by the comma and put each word into another table with one column and then group and count. There are several good string parsing functions available on the SSC site.
If the description isn't broken up somehow, I don't know how you'd ever get 'all wheel drive' as category, since there's nothing to indicate that these 3 words should go together.
September 1, 2010 at 5:40 pm
Thanks so much for the help, I will give it a go and hopefully save a lot of time!!
I found this that may help me (and others!!)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply