Get substring in sql server

Question

Hi Guys,

I want to get substring in sql server from right split on dot('.').

I have a column which have file name hello.exe , i want to find extension of file exactly as in C# Path.GetExtension("filename") does.

Thanks Rajesh

Answer 1

DECLARE @originalstring VARCHAR(100)
SET @originalstring = 'hello.exe'

DECLARE @extension VARCHAR(50)

SET @extension = SUBSTRING(@originalstring, CHARINDEX('.', @originalstring) + 1, 999)

SELECT @extension

That should do it, I hope! This works as long as you only have a single '.' in your file name - separating the file name from the extension.

Marc

Answer 2

You can use reverse along with substring and charindex to get what you're looking for:

select
    reverse(substring(reverse(filename), 1, 
        charindex('.', reverse(filename))-1)) as FileExt
from
    mytable

This holds up, even if you have multiple . in your file (e.g.-hello.world.exe will return exe).

So I was playing around a bit with this, and this is another way (only one call to reverse):

select 
    SUBSTRING(filename, 
        LEN(filename)-(CHARINDEX('.', reverse(filename))-2), 8000) as FileExt
from
    mytable

This calculates 10,000,000 rows in 25 seconds versus 29 seconds for the former method.