blog.cyberman.it

Credo che a qualcuno possano essere utili queste funzioni T-SQL per SQL Server che permettono di suddividere le varie porzioni che compongono una Path.

CREATE FUNCTION DBO.spString_LastIndexOf(@inputstring VARCHAR(8000), @searchString CHAR)
RETURNS INT
AS
BEGIN
  DECLARE @myindex int,
 @index int
  SELECT @inputstring = RTRIM(LTRIM(@inputstring))
  SELECT @index = 0
  SELECT @myindex = CHARINDEX(@searchString, @inputstring, @index)
  WHILE  @myindex <> 0
  BEGIN
SELECT @index = @myindex
SELECT @myindex = CHARINDEX(@searchString, @inputstring, @index+1)
  END
  RETURN (@index)
END
GO


CREATE FUNCTION dbo.spPath_GetFilePath(@inputstring VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @index int
 
 
  SET @index = dbo.spString_LastIndexOf(@inputstring, '\');
 
  return SUBSTRING(@inputstring, 1, @index) 
END
GO

CREATE FUNCTION dbo.spPath_GetFileName(@inputstring VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @index int
 
 
  SET @index = dbo.spString_LastIndexOf(@inputstring, '\');
 
  return SUBSTRING(@inputstring, @index + 1, 8000)
END
GO

CREATE FUNCTION dbo.spPath_GetFileNameWithoutExtension(@inputstring VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @index int
 
  SET @inputstring = dbo.spPath_GetFileName(@inputstring); 
  SET @index = dbo.spString_LastIndexOf(@inputstring, '.');
 
  return SUBSTRING(@inputstring, 1, @index -1)
END
GO

ALTER FUNCTION dbo.spPath_GetFileExtension(@inputstring VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @index int
 
  SET @inputstring = dbo.spPath_GetFileName(@inputstring); 
  SET @index = dbo.spString_LastIndexOf(@inputstring, '.');
 
  return SUBSTRING(@inputstring, @index, 8000)
END
GO

SELECT dbo.spPath_GetFilePath('C:\prova\filename.txt')
SELECT dbo.spPath_GetFileName('C:\prova\filename.txt')
SELECT dbo.spPath_GetFileNameWithoutExtension('C:\prova\filename.txt')
SELECT dbo.spPath_GetFileExtension('C:\prova\filename.txt')