Friday, March 9, 2012

Function for getting a extension from a filename

I need a function that returns the extension of a filename, im not so the T-SQL expert so i wanted to ask if this query is ok?

would it be faster to do this as a CLR function?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fn_GetFileExtension]
(
@.Name nvarchar(256)
)

RETURNS nvarchar(256)
AS
BEGIN
IF ( SUBSTRING( @.Name, LEN(@.Name) - 3, 1 ) = '.' )
RETURN LOWER(SUBSTRING( @.Name, LEN(@.Name) - 2, 3 ));

DECLARE @.i int;
SELECT @.i = 1;

WHILE ( @.i < LEN(@.Name) )
BEGIN
IF ( SUBSTRING( @.Name, LEN(@.Name) - @.i, 1 ) = '.' )
RETURN LOWER(SUBSTRING( @.Name, LEN(@.Name) - @.i + 1, @.i ));
ELSE
SELECT @.i = @.i + 1;
END

RETURN '';
END

In .NET, you can use the FileInfo class:

return new FileInfo(filename).Extension;

You need to run some tests to see if that's faster than SQL, though...|||I would recommend that you use some other method personally. If part of the consumer is using .NET or if you are using SQL 2k5 then you have Path.GetExtension (a static method that when provided with a filename returns the extension).
|||i tried the clr way (with Path.GetExtension) and it's the faster solution. it's over 10 times faster than the sp, that's much more than i expected...|||Yep. Glad the problem was solved.

No comments:

Post a Comment