Actually you can write an anonymous PL/SQL block inside a cfquery tag. There's a bug wherein line returns will cause an error in the JDBC driver, so the block must be all on one line, but it will work.
Anyway, SUBSTR() will work like a right() function when -1 is used as the starting position: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions119a.htm And here's the CASE syntax for Oracle: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm On 2/11/07, Dave Phillips <[EMAIL PROTECTED]> wrote: > Jim, > > Yeah, I typoed. Sorry, worked with SQL Server a lot. I did mean a PL/SQL > solution, however, I cannot write a PL/SQL function to utilize, so whatever > SQL I use, it has to work inside a <cfquery> > > To my knowledge, Oracle doesn't have a right() function, and the sql provided > didn't work in Oracle. Also, I could have extensions with any number of > digits, so I'm not sure that will work anyway. > > Still looking for a solid solution. > > Kris Jones wrote: > > select case left(right(path,4),1) when '.' then right(path,3) else '' end > > > > This will work on SQL Server (assuming all of your extensions are three > characters), and possibly on Oracle(I think it now supports CASE). In > your message you wanted a T-SQL solution, but said your database was > Oracle. Did you mean to say a PL/SQL or possible just SQL solution? > T-SQL is the proprietary version of SQL specific to Sybase and SQL Server. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269440 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
