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

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

LVL 3
Eric BourlandAsked:
Who is Participating?
 
_agx_Commented:
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
 
Eric BourlandAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.