--USE [TempTable]
--GO
/****** Object: UserDefinedFunction [dbo].[fn_GetFileInfoInFullPathUsingXml] Script Date: 2017-01-17 오후 1:43:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------------------------------------------------
-- 파일경로에서 파일정보 가져오기
-- 파일경로 문자열에서 파일명,경로,확장자가지오기(filepath:경로,filename:파일명,extension:확장자)
-- 예: SELECT 데이터베이스명.소유자명.fn_GetFileInfoInFullPathUsingXml('filename','test.xml','/')
-- SELECT dbo.fn_GetFileInfoInFullPathUsingXml('filename','c:/dkdk/test.xml','/')
-- SELECT dbo.fn_GetFileInfoInFullPathUsingXml('filepath','c:/dkdk/test.xml','/')
-- SELECT dbo.fn_GetFileInfoInFullPathUsingXml('extension','c:/dkdk/test.xml','/')
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-- 프로시저 생성
-----------------------------------------------------------------------------------------------------------
ALTER FUNCTION [dbo].[fn_GetFileInfoInFullPathUsingXml]
(
@iGubun NVARCHAR(10), -- filepath:경로,filename:파일명,extension:확장자
@iFullPath NVARCHAR(MAX),
@iDelimiter NVARCHAR(10)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @wTblSplit AS TABLE
(
Seq INT IDENTITY(1,1),
Fragment NVARCHAR(MAX)
)
DECLARE @wFileInfo NVARCHAR(MAX)
DECLARE @wXml XML
DECLARE @wMax INT
SELECT @wFileInfo = ''
SELECT @wMax = 0
SET @wXml = N'' + REPLACE(@iFullPath, @iDelimiter,'') + ''
INSERT INTO @wTblSplit(Fragment)
SELECT r.value('.','NVARCHAR(MAX)') as item
FROM @wXml.nodes('//root/r') as records(r)
SELECT @wMax = MAX(Seq) FROM @wTblSplit
IF @iGubun = 'filepath'
BEGIN
IF @wMax = 1
BEGIN
SELECT @wFileInfo = '.'
END
ELSE
BEGIN
SELECT @wFileInfo = COALESCE(@wFileInfo + @iDelimiter + Fragment, Fragment)
FROM @wTblSplit
WHERE Seq < @wMax
ORDER BY Seq
SELECT @wFileInfo = SUBSTRING(@wFileInfo,2,LEN(@wFileInfo)) + '/'
END
END
ELSE IF @iGubun = 'filename'
BEGIN
SELECT @wFileInfo = Fragment FROM @wTblSplit WHERE Seq = @wMax
SELECT @wFileInfo = SUBSTRING(@wFileInfo,1,CHARINDEX('.',@wFileInfo))
END
ELSE IF @iGubun = 'extension'
BEGIN
SELECT @wFileInfo = Fragment FROM @wTblSplit WHERE Seq = @wMax
SELECT @wFileInfo = SUBSTRING(@wFileInfo,CHARINDEX('.',@wFileInfo)+1,LEN(@wFileInfo)-CHARINDEX('.',@wFileInfo))
END
RETURN LTRIM(RTRIM(@wFileInfo))
END