Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to sort by BusinessTitle, but omit the definite article "The" from the sort order?

Posted on 2011-02-24
2
Medium Priority
?
367 Views
Last Modified: 2012-05-11
This is an interesting, I hope, question about sort results in the ASC Business Directory.

In my ColdFusion page, category.cfm, I sort query results by BusinessTitle. Now I need to sort by BusinessTitle, and omit the definite article "The" from the sort order.

So, BusinessTitles like "The SSI Group, Inc" and "The Top Notch Medical Services" would be sorted as if their names were

SSI Group, Inc
Top Notch Medical Services

Could I add a CFIF statement to the ORDER BY clause in my query?

<cfquery name="getBusinesses" datasource="#application.datasource#">   
  SELECT *   
   FROM Businesses biz  
    LEFT OUTER JOIN BizHasCat bhc ON biz.BusinessID = bhc.BusinessID   
    LEFT OUTER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID   
   <cfif val(url.BusinessCategoryID)>   
    INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID   
    AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">   
   </cfif>
   
   <cfif BusinessTitle LIKE '[The ]'>

.... some syntax to tell ColdFusion to disregard [The ] ...
   
   <cfelse>
  ORDER BY BusinessTitle ASC, BusinessCategory ASC   
   </cfif>

</cfquery>

Open in new window


Am I on the right track here? I would imagine other people have faced this problem of omitting the "The " (definite article, with trailing space) from sort order.

Thank you for any advice.

Eric
<!----- 
Name:        category.cfm 
Author:      Eric B, _agx_, gdemaria 
Description: Display ASCA businesses by category; ORDER BY BusinessTitle ASC, BusinessCategory ASC; omit "The" from sort order
Created:     February 2011 
ColdFusion Version 8 
MS SQL Server 2005 
-----> 
 
<cfparam name="url.BusinessCategoryID" default=""> 
 
<cfquery name="getBusinesses" datasource="#application.datasource#">   
  SELECT *   
   FROM Businesses biz  
    LEFT OUTER JOIN BizHasCat bhc ON biz.BusinessID = bhc.BusinessID   
    LEFT OUTER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID   
   <cfif val(url.BusinessCategoryID)>   
    INNER JOIN BizHasCat sel ON sel.BusinessID = biz.BusinessID   
    AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">   
   </cfif>   
  ORDER BY BusinessTitle ASC, BusinessCategory ASC   
</cfquery>  
 
<cfset activeCategoryName = ""> 
<cfif val(url.BusinessCategoryID)> 
  <cfquery name="getCat" datasource="#application.datasource#"> 
   select BusinessCategory 
    from Categories 
   where BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">   
  </cfquery> 
  <cfset activeCategoryName = getCat.BusinessCategory> 
</cfif> 
  
<!--- site header ---> 
<cfinclude template="/SiteHeader.cfm">

   
<p class="align-center"><a href="/businessdirectory/">Return to ASC Supporter Business Directory</a></p>
  
  
<h1>ASC Supporter Business Directory</h1> 
  
<cfif len(activeCategoryName)> 
<h2 class="h2_business"><cfoutput>#activeCategoryName#</cfoutput></h2> 
</cfif> 

     
    
<cfoutput query="getBusinesses" group="BusinessTitle" groupcasesensitive="no">   
    
<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Categories:</strong><br />   
<cfoutput group="BusinessCategory" groupcasesensitive="no">

<cfif getBusinesses.businesscategory IS "">
(None listed.)
<cfelse>
#getBusinesses.BusinessCategory#<br />
</cfif>


</cfoutput>   
</div>   




   
   
<p><span class="bold blue00467f">#BusinessTitle#</span><br />   
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>   
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>   
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>   
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>   
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />   
<strong>Voice:</strong> #BusinessTelephone#<br />   
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>   
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>   
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /></cfif>    
   
<cfif BusinessDescription IS NOT ""><br /><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>   
   
   
<hr class="clear-both" />   
   
</cfoutput>  
 
  
  
 
  
<!--- site footer ---> 
<cfinclude template="/SiteFooter.cfm">

Open in new window

0
Comment
Question by:Eric Bourland
2 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 34974201
There's probably a more elegant way, but a simple CASE in the ORDER BY would do it

ORDER BY
case
      WHEN BusinessTitle LIKE 'The _%' then right(BusinessTitle, Len(BusinessTitle)-4)
      ELSE BusinessTitle
end
, BusinessCategory ASC  



0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 34974431
Worked perfectly. I was reading up on CASE in my Forta SQL book but could not figure out the right syntax. This solution makes sense. Thank you as always, _agx_.

Eric B
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

972 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