-- ----------------------------
-- function structure for getFileName
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[getFileName]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
DROP FUNCTION[dbo].[getFileName]
GO
CREATE FUNCTION [dbo].[getFileName]
( @FileName AS varchar(255)
)
RETURNS varchar(225)-- nvarchar
AS
BEGIN
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server'
declare @name varchar(255)
declare @length int
declare @i int
set @length = LEN(@FileName)
set @i =2
set @name ='????'
WHILE (@i < @length )AND( @length<> 0) BEGIN
set @name = RIGHT(@FileName, @i)
IF LEFT(@name,1) = '.'
BEGIN
set @name = RIGHT(@name, @i-1)
set @i = @length
END
else
BEGIN
SET @i = @i+1
SET @name = '???'
END
END
RETURN @name
END
GO
使用
SELECT
a.file_name,
dbo.getFileName(a.file_name)
from
base_resource a
|