SQLTeam.com | Weblogs | Forums

How to get this access script to work in SQL



Basically, what I want to do is take anything in the filename after the period and move to another column called extension, and if there is no period bring over the text to the extension column

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];


Is there a SQL Function equivalent to InStrRev Function

Script how to remove extension and put in separate column


It looks a little messy because I am trying to take care of cases where there is no period in the file name, or where there is more than one period in the filename. If those are not possible cases, it can be simpler.


yes thanks