Hey, I'm wondering if anyone would know T-SQL I could use to accomplish the following (I don't have the luxury of writing an SQL function for this, unfortunately): I have a column called 'path' in a database (Oracle) that has the following types of values in it: Example 1: c:\foldername\documentname.ext (standard format) Example 2: c:\foldername\subfolder\my.document.name.ext (multiple periods) Example 3: c:\foldername\documentname (no extension) What I want to do is be able to extract the extension from the path in a select statement. I know I can do this easily in CF code, I'm trying to avoid looping through a large query and doing it if at all possible. Here's what I have so far, but it's too simple and doesn't work on example 2 above: select decode(inStr(Path,'.'),0,'',subStr(path,inStr(path,'.')+1,3)) from document I suppose I need to find a way to do something like a 'rightInStr()' or something like that. A way to get the position of the LAST period. Or if someone has some other idea of how I can do it in T-SQL, maybe using some CASE statements or something? I'm not great with CASE and SQL. Please, if anyone can help, I would greatly appreciate it! Sincerely, Dave Phillips WebTech Staffing, LLC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:269408 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
