CREATE FUNCTION MssqlINSTR(@OriginalText VARCHAR(8000),@GubunText VARCHAR(100),@Pos INT)

RETURNS VARCHAR(8000)  

BEGIN 

       DECLARE @CleanedText VARCHAR(8000)

       DECLARE @ReplaceText VARCHAR(8000)

       DECLARE @Minus int ,@I INT ,@TempPOS INT ,@NextTempPOS INT

 

       SET @OriginalText =  @GubunText + @OriginalText + @GubunText

       SET @ReplaceText = REPLACE(@OriginalText ,@GubunText,'')

 

       SET @Minus = len(@OriginalText) - len(@ReplaceText)

    IF  @Minus - 1 < @Pos

    BEGIN

        RETURN ''

    END

    IF  @Pos < 1

    BEGIN

        RETURN ''

    END

 

    SET @TempPOS = 0

    SET @I = 0

       WHILE @Pos > @I

       BEGIN

        SET @TempPOS = CHARINDEX(@GubunText, @OriginalText , @TempPOS + 1 )

        SET @NextTempPOS = CHARINDEX(@GubunText, @OriginalText , @TempPOS + 1 )

             SET @I = @I + 1

       END 

 

       RETURN SUBSTRING(@OriginalText ,@TempPOS + 1 ,@NextTempPOS - @TempPOS - 1)   

END

 

WITH TEST

AS (

     SELECT '123,qqq,456,qweasd' TEXTA UNION ALL

     SELECT '12TT3,3Y45,4566,6778DD,YF' TEXTA UNION ALL

     SELECT '1RR23,34U5,5456,678445DD,YOF' TEXTA UNION ALL

     SELECT '123OO,3O45,45O6,678DD,Y8F,LLKJ' TEXTA 

)

 

SELECT A.TEXTA  , B.NUM ,DBO.MssqlINSTR(A.TEXTA,',',B.NUM) DATA

  FROM TEST AS A

CROSS APPLY

  (SELECT NUMBER + 1 NUM

    FROM MASTER.DBO.SPT_VALUES

   WHERE TYPE = 'P'

     AND NUMBER <=  (LEN(A.TEXTA) - LEN(REPLACE(A.TEXTA ,',','')) )  )  AS B

WHERE ISNUMERIC(DBO.MssqlINSTR(A.TEXTA,',',B.NUM) ) = 1

이 게시물을...




profile