Solved

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

Posted on 2011-02-24
2
343 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 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now