파일경로에서 파일정보(파일명,경로,확장자) 가져오기

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

11

http://rocabilly.tistory.com/88 에 있던걸 extension 부분만 수정했다.

aaa.bbb.ccc.jpg 처럼 dot('.')이 여러개일경우 오류가 발생할수 있다.