SQLTeam.com | Weblogs | Forums

How to get this access script to work in SQL

tsql
sql2008
sql2012
sql2014
sql2008r2

#1

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


#2

Is there a SQL Function equivalent to InStrRev Function


Script how to remove extension and put in separate column
#3

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.


#4

yes thanks