Author |
Topic |
token
Posting Yak Master
133 Posts |
Posted - 2013-07-06 : 16:41:49
|
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
Aged Yak Warrior
545 Posts |
Posted - 2013-07-06 : 19:52:33
|
how about: where name like 'M%' or name like ' M%'? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-07 : 10:35:34
|
the way you explain I think what you want is something like WHERE ' ' + Name like '% M%' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-07 : 10:55:40
|
With a minor modification bitsmed's code works and is more comprehensiblequote: Originally posted by bitsmed how about: where name like 'M%' or name like '% M%'?
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-07 : 11:48:59
|
quote: Originally posted by MuMu88 With a minor modification bitsmed's code works and is more comprehensiblequote: Originally posted by bitsmed how about: where name like 'M%' or name like '% M%'?
I've covered both scenario using single condition------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-07 : 12:55:12
|
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 comprehensiblequote: Originally posted by bitsmed how about: where name like 'M%' or name like '% M%'?
I've covered both scenario using single condition------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-07 : 13:02:39
|
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 comprehensiblequote: Originally posted by bitsmed how about: where name like 'M%' or name like '% M%'?
I've covered both scenario using single condition------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
token
Posting Yak Master
133 Posts |
Posted - 2013-07-10 : 18:45:28
|
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 :( |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-11 : 01:42:38
|
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 outYou may post solution if you feel others will benefit out of it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|