--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
  SELECT A.TABLE_NAME, B.COMMENTS
    FROM ALL_TABLES A, ALL_TAB_COMMENTS B
   WHERE A.TABLE_NAME = B.TABLE_NAME AND A.TABLE_NAME LIKE :table_name || '%'
ORDER BY A.TABLE_NAME
종종 사용하는데 쓸때마다 잊어서 메모해놓자.
저작자 표시
신고
Posted by 두억시니 트랙백 0 : 댓글 0
 SELECT COLUMN_ID AS SEQ,
         (SELECT NVL (D.POSITION, '')
            FROM ALL_CONS_COLUMNS D, all_constraints E
           WHERE     D.OWNER = UPPER ( :user_name)
                 AND D.OWNER = A.OWNER
                 AND D.TABLE_NAME = A.TABLE_NAME
                 AND D.COLUMN_NAME = A.COLUMN_NAME
                 AND D.OWNER = E.OWNER
                 AND D.TABLE_NAME = E.TABLE_NAME
                 AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                 AND E.CONSTRAINT_TYPE = 'P')
            AS PK,
         (SELECT CASE WHEN D.POSITION IS NULL THEN '' ELSE 'V' END
            FROM ALL_CONS_COLUMNS D, all_constraints E
           WHERE     D.OWNER = UPPER ( :user_name)
                 AND D.OWNER = A.OWNER
                 AND D.TABLE_NAME = A.TABLE_NAME
                 AND D.COLUMN_NAME = A.COLUMN_NAME
                 AND D.OWNER = E.OWNER
                 AND D.TABLE_NAME = E.TABLE_NAME
                 AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                 AND E.CONSTRAINT_TYPE = 'R')
            AS FK,
         A.COLUMN_NAME AS COLUMN_ID,
         C.COMMENTS,
         A.DATA_TYPE,
         A.DATA_LENGTH,
         A.NULLABLE,
         A.DATA_DEFAULT
    FROM ALL_TAB_COLUMNS A, ALL_TAB_COMMENTS B, ALL_COL_COMMENTS C
   WHERE     A.OWNER = UPPER ( :user_name)
         AND B.OWNER = UPPER ( :user_name)
         AND C.OWNER = UPPER ( :user_name)
         AND A.OWNER = B.OWNER
         AND A.OWNER = C.OWNER
         AND A.TABLE_NAME = B.TABLE_NAME
         AND A.TABLE_NAME = C.TABLE_NAME
         AND A.COLUMN_NAME = C.COLUMN_NAME
         AND A.TABLE_NAME = UPPER ( :table_name)
ORDER BY 1
MSSQL은 어떻게 할까나...


저작자 표시
신고
Posted by 두억시니 트랙백 0 : 댓글 0