Function for getting a extension from a filename

Hot Topic

job failing
triggers and record data question
Merge 2 Databases?
The syntax of the SQL the DataSet Design Surface generates
Cannot figure out how to write this script?
Number format with negatives
Generating Script
exec statement
SQL Express and Reporting Services not available until June 2006?
Corruption of package?!

SQL Server

CLR SP to Transform Xml (SecurityException)
how to set SQL Server OLE DB provider index option , It gives error ?
SP2 - Need Your Feedback!
I want make table 4 like this one in SQL server 2005
Full-Text Search - How to determine word offset in CONTAINS query.
to get the latest price value
Running an INSERT statement a @variable without using EXEC
Tuning Advisor errors
How to get detailed info of views?
SQLExpress & Reporting Services

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

Answer this question

Function for getting a extension from a filename

  • CarlMalden

    Yep. Glad the problem was solved.

  • Keith WIlliams

    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).

  • Alex Aidar

    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...

  • Mentz

    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...
  • Function for getting a extension from a filename
Answer this question