T-SQL Extractor

來源:互聯網
上載者:User

/******************************************************************************
 * Author: iret
 * Desc: T-SQL Extractor
 *           Extract the comments and blanks and tabs from the SQL statement
 * 為了比較兩個預存程序,或者SQL語句是否一致,抽空寫了一個可以刪除T-SQL 陳述式中的注釋和空格的指令碼,挺精緻的樣子。
 * Created Date: 2004/10/21
 ******************************************************************************/

DECLARE @script VARCHAR(8000), @extractedScript VARCHAR(4000)
SET @script = ''
/*從系統資料表擷取預存程序的指令碼*/
SELECT @script = @script + [text]
FROM syscomments, sysobjects
WHERE
    syscomments.[id] = sysobjects.[id] AND sysobjects.[name] LIKE '%campa_AppSegment'

/*標誌符*/
DECLARE @InLineCommented BIT, @InSectionCommented BIT, @InString BIT, @position INT

/*當前字元*/
DECLARE @curChar INT

/*前一個字元*/
DECLARE @preChar INT

SET @InLineCommented = 0
SET @InSectionCommented = 0
SET @InString = 0

SET @extractedScript = ''
SET @position = 1
SET @preChar = null

WHILE @position <= DATALENGTH(@script)
BEGIN
    --擷取當前字元
    SET @curChar = ASCII(SUBSTRING(@script, @position, 1))
    IF @preChar = ASCII('/') AND @curChar = ASCII('*') AND @InLineCommented = 0 AND @InString = 0
    BEGIN
        -- SET the sign in section comment
        SET @InSectionCommented = 1

         --pop the / char
         SET @extractedScript = substring(@extractedScript,1,len(@extractedScript)-1)

         SET @preChar = @curChar
         SET @position = @position + 1
         CONTINUE
    END

    IF @preChar = ASCII('*') AND @curChar = ASCII('/') AND @InLineCommented = 0 AND @InString = 0
    BEGIN
         SET @InSectionCommented = 0
         SET @preChar = @curChar
         SET @position = @position + 1
         CONTINUE
    END

    IF @preChar = ASCII('-') AND @curChar = ASCII('-') AND @InSectionCommented = 0 AND @InString = 0
    BEGIN
         SET @InLineCommented = 1

         --pop the / char
         SET @extractedScript = substring(@extractedScript,1,len(@extractedScript)-1)

         SET @preChar = @curChar
         SET @position = @position + 1
         CONTINUE
    END

    IF @curChar = ASCII('''') AND @InString = 0 AND @InSectionCommented = 0 AND @InLineCommented = 0
    BEGIN
         SET @InString = 1
    END

    IF @inString = 1 AND @curChar = ASCII('''')
    BEGIN
         IF ASCII(SUBSTRING(@script, @position+1, 1))= ASCII('''')
         BEGIN  
              SET @extractedScript = @extractedScript + ''''
              SET @position = @position + 1
        END
        ELSE
         BEGIN
              SET @InString = 0  
         END
    END

    IF @InSectionCommented = 1
    BEGIN
         SET @preChar = @curChar
         SET @position = @position + 1
         CONTINUE
    END

    IF @InLineCommented = 1
    BEGIN
        -- if meets the end of the line set the InLineCommented to false
        IF @curChar = 10 AND @preChar = 13
        BEGIN
           SET @InLineCommented = 0
        END

         SET @preChar = @curChar
         SET @position = @position + 1
         CONTINUE
    END

    IF @curChar = ASCII(' ') OR @curChar = 10 OR @curChar =13 OR @curChar = ASCII(' ') OR @curChar = 32
    BEGIN
         SET @preChar = @curChar
         SET @position = @position + 1
         CONTINUE
    END

    SET @extractedScript = @extractedScript + CHAR(@curChar)
    SET @preChar = @curChar
    SET @position = @position + 1
END

-- print the result script
SELECT @extractedScript

 

 

 


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。