SQLTeam.com | Weblogs | Forums

Removing data out of output last 3 characters

tsql
sql2008
sql2012

#1

I was wondering if I could get a little help, I have the following script.


select Sys.Name0, Sys.User_Name0, SF.FilePath, SF.Filename,

Count(SF.FileName) as FileCount,
Sum(SF.FileSize/1024/1024) as 'Used Space'

from v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID = SF.ResourceID
where SF.FilePath like '%'+'Users'+'%' +'%documents%' and SF.FileSize > 0 Group by Sys.Name0, Sys.User_Name0, SF.Filename, SF.FilePath

having Count(SF.FileName) > 0 order by Sys.Name0 Desc


The problem is, I am getting too much info, I would like not to count, any files ending in .exe, .dll, .ico, etc.. any idea how I could do this? A little help for a sql novice?


#2

add (i.e. BEFORE the GROUP BY)

AND SF.Filename NOT LIKE '%.exe'
AND SF.Filename NOT LIKE '%.dll'
...

If all the File Extensions that you are checking for are 3 characters long it might be better (but might be slower) to do:

AND RIGHT(SF.Filename, 4) NOT IN ('.exe', '.dll', ...)