what I come out is something like the following script and I am really not satisfied with it, let alone it processes 3 letter extension only and I need to also cover 4 letter extension.

select left(reverse(left(reverse(breach_content),charindex('.',reverse(breach_content))-1)), 3), *

from reporting_dailydlpdetail_20150730

where charindex('.',reverse(breach_content))-1 <> -1

and

left(reverse(left(reverse(breach_content),charindex('.',reverse(breach_content))-1)), 3)

in

('bmp',

'aac',

'mov',

'swf',

'wmv',

'MPG*',

'mkv',

'ogg',

'm4v',

'3gp',

'AVI*',

'asf',

'wav',

'ogv',

'tif',

'flv',

'MP3',

'jpg',

'wma',

'oga')