SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Categorising Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 07/06/2013 :  16:41:49  Show Profile  Reply with Quote
I have a list of names (e.g. company names), and want to display this as a directory/list on a webpage.

I'd like for users to be able to search by alphabetical letter. So if they click on 'M' then it will show names that begin with M.

However not all companies that should be in the 'M' category will start with the letter M e.g. University of Michigan. So using
WHERE Name LIKE 'M%'
will not work here.

Theoretically, what would be the best way to go about categorising data in this way? Is there a better way than adding a column in the table and manually assign a letter to each company name?

bitsmed
Constraint Violating Yak Guru

388 Posts

Posted - 07/06/2013 :  19:52:33  Show Profile  Reply with Quote
how about: where name like 'M%' or name like ' M%'?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/07/2013 :  10:35:34  Show Profile  Reply with Quote

the way you explain I think what you want is something like

WHERE ' ' + Name  like '% M%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/07/2013 :  10:55:40  Show Profile  Reply with Quote
With a minor modification bitsmed's code works and is more comprehensible
quote:
Originally posted by bitsmed

how about: where name like 'M%' or name like '% M%'?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/07/2013 :  11:48:59  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

With a minor modification bitsmed's code works and is more comprehensible
quote:
Originally posted by bitsmed

how about: where name like 'M%' or name like '% M%'?




I've covered both scenario using single condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/07/2013 :  12:55:12  Show Profile  Reply with Quote
Your solution might be more elegant and concise but the other one seems more understandable and hence cost effective from a maintainability perspective.

quote:
Originally posted by visakh16

quote:
Originally posted by MuMu88

With a minor modification bitsmed's code works and is more comprehensible
quote:
Originally posted by bitsmed

how about: where name like 'M%' or name like '% M%'?




I've covered both scenario using single condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/07/2013 :  13:02:39  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Your solution might be more elegant and concise but the other one seems more understandable and hence cost effective from a maintainability perspective.

quote:
Originally posted by visakh16

quote:
Originally posted by MuMu88

With a minor modification bitsmed's code works and is more comprehensible
quote:
Originally posted by bitsmed

how about: where name like 'M%' or name like '% M%'?




I've covered both scenario using single condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





hmm...its very much similar to posted solution with just a small minor modification.
Didnt get how it will make so difficult for someone to understand?
I dont think it will do much change in maintainability aspect either!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 07/10/2013 :  18:45:28  Show Profile  Reply with Quote
Thank you for the solution, I tried many variations but it didn't quite work how I wanted. In the end I had to manually create a new column and assign letters to names to categorise them :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/11/2013 :  01:42:38  Show Profile  Reply with Quote
quote:
Originally posted by token

Thank you for the solution, I tried many variations but it didn't quite work how I wanted. In the end I had to manually create a new column and assign letters to names to categorise them :(


OK..Glad that you got it sorted out
You may post solution if you feel others will benefit out of it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000