'extension'에 해당되는 글 1건

  1. 2017.01.17 파일경로에서 파일정보(파일명,경로,확장자) 가져오기
--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


http://rocabilly.tistory.com/88 에 있던걸 extension 부분만 수정했다. aaa.bbb.ccc.jpg 처럼 dot('.')이 여러개일경우 오류가 발생할수 있다.


Posted by 두억시니 트랙백 0 : 댓글 0