SQLTeam.com | Weblogs | Forums

Script how to remove extension and put in separate column

I need to take the column filename and just create a new column called extension after the ‘.’ This needs to be a View called extensions

This script works in ACCESS, but there seems to be no equivalent to -InStrRev

SELECT [UserProfile12_7-512-1mb].FileName, Right([Filename],Len([Filename])-InStrRev([Filename],".")) AS Extension, [UserProfile12_7-512-1mb].ComputerName, [UserProfile12_7-512-1mb].KB FROM [UserProfile12_7-512-1mb];

Then I need to create another script using the New View extensions and join another table and group by the last extension In ACCESS the script is this

SELECT ExtensionSheet.Extension, Sum([UserProfile12_7-512-1mb].KB) AS SumOfKB FROM [UserProfile12_7-512-1mb] INNER JOIN ExtensionSheet ON [UserProfile12_7-512-1mb].ComputerName = ExtensionSheet.ComputerName GROUP BY ExtensionSheet.Extension;

Does the reply I posted in the other thread answer your question?