how about something that looks at the right 4 characters, and if the first of those 4 is a period, then get the right 3. I don't have SQL Server on this box, but it would be something like:
select case left(right(path,4),1) when '.' then right(path,3) else '' end Again, don't have it in front of me, but, give it a try. Cheers, Kris > 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:269410 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
