A SQL Server file ‘basename’ function

By kenglish

Given a file path: /var/www/html/index.html
Returns: index.html

Pretty common, here’s how you do it:

Perl:

use File::Basename; 
$fullname = "/usr/local/src/perl-5.6.1.tar.gz"; 
$file = basename($fullname);

PHP:

$path = "/home/httpd/html/index.php";
$file = basename($path);

Ruby:

path = "/usr/lib/ruby/site_ruby/1.8/rubygems/version.rb"
File.basename path

Python:

import os.path
path =  "/usr/local/bin/python"
os.path.basename(path)

T-SQL (MsSQL Server):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE FUNCTION [dbo].[fn_file_basename]
(
	-- Add the parameters for the function here
	@file_path VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @file_basename VARCHAR(255)
    IF charindex('\', @file_path) != 0 
	   set @file_basename= reverse(substring(reverse(@file_path), 1, charindex('\', reverse(@file_path))-1))
	else
	   set @file_basename=@file_path
 
	return @file_basename
END

My brilliant co-worker figured this out. The magic is done on line 11:

reverse(SUBSTRING(reverse(@file_path), 1, charindex('\', reverse(@file_path))-1))

It reverses the string, find the first occurance of the character ‘\’, takes the substring to that character and the reverses again. How elegant!



categoriaProgramming commentoNo Comments dataNovember 24th, 2009

About... kenglish

This author published 76 posts in this site.

Share

FacebookTwitterEmailWindows LiveTechnoratiDeliciousDiggStumbleponMyspaceLikedin

Leave a comment