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

REVERSE(STUFF(REVERSE([Filename]),PATINDEX('%.%',REVERSE([Filename])),LEN([Filename]),''))

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