Reputation: 6589
I want to get a substring in SQL Server from last sequence of a split on dot (.
).
I have a column which contains file names such as hello.exe
, and I want to find the extension of the file exactly as Path.GetExtension("filename")
does in C#.
Upvotes: 18
Views: 32107
Reputation: 1336
The following SQL request adressed most of the edge cases in my weird database where many files didn't have extensions.
select distinct reverse(left(reverse(fileNameWithExtension), charindex('.', reverse(fileNameWithExtension)) - 1))
from myTable
where charindex('.', reverse(fileNameWithExtension)) - 1 > 0 and charindex('.', reverse(fileNameWithExtension)) - 1 < 7 and fileNameWithExtension is not null
Upvotes: 0
Reputation: 4994
Same as accepted answer, but I've added a condition to avoid error when filename
is null or when filename
has no extension (no point):
select
reverse(substring(reverse(filename), 1,
charindex('.', reverse(filename))-1)) as FileExt
from
mytable
where
filename is not null
and charindex('.',filename) > 0
Upvotes: 1
Reputation: 21
Try this
SELECT RIGHT(
'C:\SomeRandomFile\Filename.dat',
CHARINDEX(
'.',
REVERSE(
'C:\SomeRandomFile\Filename.dat'
),
0)
-1)
Upvotes: 2
Reputation: 95243
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.
Upvotes: 30
Reputation: 755531
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
Upvotes: 2