Thanks to everyone who responded. I guess this is going to take a few
minutes to do...
Maybe I should write a cfc that does this???
For future posterity??
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 1/8/2003 11:47:33 PM >>>
In a perfect world, you use regular expressions, but you can probably
get
most of the work done using the replace() function in your dialect of
SQL,
or failing that, using the ColdFusion function. It's pretty ugly -- in
MSSQL
you could use a User-defined function or a view to make a table that
doesn't
look so nasty
SELECT blahblah Replace(LEFT(title,3),"the","") as cleanTitle
-snip-
ORDER BY cleanTitle
handles "the"
you could nest them to do something like
c
or if you can support something like the T-SQL CASE (using sybase or
mssql)
you could use CASE and the LEFT or PATINDEX index function to chop
things
up -- something like (off the top of my head)
SELECT cleantitle= CASE title
WHEN LEFT(title,3) ='the' THEN
SUBSTRING(title,4,len(title))
ELSE title
END
or something along those lines.
A UDF would be great way to implement because then you can change the
underlying mechanics (say from replace to CASE to regex) without
recoding
SELECT cleantitle(title) as cleanedTitle
ORDER BY cleanedTitle
Regards,
John Paul Ashenfelter
CTO/Transitionpoint
[EMAIL PROTECTED]
----- Original Message -----
From: "Candace Cottrell" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 08, 2003 6:48 PM
Subject: ORDER BY Clause and parsing
> Good evening...
>
> I am trying to order a list of books by their Title.
> However, many of the books begin witht the word "The" or "A" or
"An".
>
> Is there an easy way to go through and say...
>
> <cfset actualTitle = query.Title minus A,An, or The>
>
> then the SQL...
>
> Select Title
> >From Books
> Order By #ActualTitle#
>
> Would I need to go through and do a left of the string Title
>
> <cfset leftVal = Left(query.Title, 3)>
> <cfif leftVal IS "the">
> <cfset actualTitle = query.title - LeftVal>
>
>
> Does this make sense??? I just dont think I'm "getting it".
>
>
> TIA :)
>
>
> Candace K. Cottrell, Web Developer
> The Children's Medical Center
> One Children's Plaza
> Dayton, OH 45404
> 937-641-4293
> http://www.childrensdayton.org
>
>
> [EMAIL PROTECTED]
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
