Solved

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

Posted on 2011-02-24
2
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

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 …
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 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