| Author |
Topic |
|
jonah002
Starting Member
4 Posts |
 Posted - 2006-01-18 : 19:20:51
|
| I have searched for answer on this and found nothing. I was thinking of writing a function that would create an extra column and strip "a,an,the" from the ordered by column and then use that column to sort, but before I ventured that route I was wondering if there was an easier way. Pretty much ordering company names and some are called "THe Company" and I wasnt the word "The" not to be considered when being ordered, this entry should be found under C instead of T.ANy help is greatly appreciated.THank you,JC |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-18 : 20:53:00
|
| Is ur Question about :U want to remove in the records where some contains the word "The " in front of company name? If so,Select CompaName from CompTbl where CompaName not like 'The %' Union AllSelect Right(Len(CompaName) - 4) from CompTbl where CompaName like 'The %' |
 |
|
|
jonah002
Starting Member
4 Posts |
Posted - 2006-01-18 : 22:11:59
|
| No what I want to do is leave the company name as is, but when I am asking to output them in alphabetical order, I want it to ignore words such as "A,AN,THE" . Therefore, in the utput a company named "The Company" should be under C instead of T in the order. |
|
|
|
jonah002
Starting Member
4 Posts |
Posted - 2006-01-18 : 22:21:24
|
| Srinika,Igonre the last comment, I never used union all ... works great! THanks! THe only typo was on your right statement, just in case anyone else finds this interesting or useful it is as follows;Right(CompaName,Len(CompaName) - 4) |
|
|
|
pomela
Starting Member
15 Posts |
Posted - 2006-01-19 : 02:47:42
|
| I prefer using REPLACE in the ORDER BY clause. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-19 : 02:51:29
|
| replace will not work correctly if the word 'The' is found somewhere else in the companame like 'The Company The'-----------------'KH' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-19 : 03:05:02
|
I think the UNION might be slow and will be tedious to maintain (keeping all the SELECT lists consistent, and the WHERE causes, so I think I would choose to do something like:SELECT *FROM MyTableORDER BY CASE WHEN CompaName LIKE 'THE %' THEN Right(Len(CompaName) - 4) WHEN CompaName LIKE 'AN %' THEN Right(Len(CompaName) - 3) ELSE CompaName Alternatively add an "MySortColumn" column to the table with the appropriate data in it - e.g. the "The" and "An " prefixes removed. This would allow ANY report to order by that column without having to have a copy of all the sorting logic. If the logic for sorting evolves over time (Where do you sort "A. B. Acme Company"? What about "AB Acme company"??) then putting that logic in, say, a Trigger so that it updated the "MySortColumn" column whenever a record was saved would mean there was only one place where changes needed to be made - and then the Reporting processes just need to ORDER BY MySortColumnKristen |
|
|
|
jonah002
Starting Member
4 Posts |
Posted - 2006-01-19 : 08:40:41
|
| This is a great forum .. I am so happy I stumbled into it, thank you all for your help |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-01-19 : 10:06:08
|
| I feel the trigger is the way to go, which would be the most efficient since you can index on that column as well. Using a UDF will be quite slow if you need to sort many rows, and in addition there can be odd situations that you would like to account for and continually editing a UDF to work with new situations is not the way to go. With a trigger, you can always manually override any entries that don't quite work.Alternately, to use a single column and not need a UDF you could set up a business rule that states all customer names must be in this form:"Company Name; The"using one single semi-colon. Then, it is easy to sort the name as well as to present it properly using a simple function to put the text after the ";" before the rest of it. this is easy done with a UDF or at the presentation layer. |
|
|
|
|
Ignoring "A,An,The" from alphabetical order