CREATE OR ALTER FUNCTION dbo.F_META_PURGE_CODE 
  (@SQL    NVARCHAR(max))
   RETURNS NVARCHAR(max)
WITH RETURNS NULL ON NULL INPUT

AS

/******************************************************************************
* MODULE : META                                                               *
* NATURE : SCALAR_FUNCTION                                                    *
* OBJECT : dbo.F_META_PURGE_CODE                                              *
* CREATE : 2018-05-28                                                         *
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://mssqlserver.fr           website: http://sqlpro.developpez.com *
* Most Valuable Professional  - MVP -  SQL Server (data platform) depuis 2004 *
*******************************************************************************
* PURPOSE : purges Transact SQL code of comments and unprintable characters   *
*******************************************************************************
* INPUTS :                                                                    *
*    @SQL NVARCHAR(max) : the Transact SQL code to be purged                  *
*******************************************************************************
* OUPUT : VARCHAR(max)                                                        *
*******************************************************************************
* RUNNABLE : version 2000 and superior                                        *
*******************************************************************************
* SAMPLE :                                                                    *
*     SELECT object_id, definition,                                           *
*            dbo.F_META_PURGE_CODE(definition)                                *
*     FROM   sys.sql_modules;                                                 *
******************************************************************************/

BEGIN

IF @SQL = N''  -- on empty string return empty string
   RETURN N'';

DECLARE @NC1    NCHAR(1),        -- char to be treated
        @NC2    NCHAR(2),        -- 2 chars to be treated
        @I      INT,             -- pos of char to be treated
        @OUT    NVARCHAR(max),   -- returned string
        @TT     BIT,             -- 1 if comment as "--"
        @SE     INT,             -- n if comment as "/* */" (can be nested !)
        @CC     BIT;             -- 1 if in as string 

-- first assignements
SELECT @OUT = N'',               -- output is empty string to begin
       @I = 1,                   -- position of first char
       @TT = 0,                  -- on the beginning, no comment "--"
       @SE = 0,                  -- on the beginning, no comment "/*"
       @CC = 0;                  -- on the beginning, no string"

-- loop while there is a char to treat
WHILE @I <= LEN(@SQL)
BEGIN

   -- getting the chars
   SELECT @NC2 = SUBSTRING(@SQL, @I, 2), -- get the 2 chars at position I
          @NC1 = SUBSTRING(@SQL, @I, 1); -- get the char at poistion I

   -- this is the beginning or the ending of a string (not in comment)
   -- so we reverse the position of the bit @CC
   IF @NC1 = '''' AND @TT = 0 AND @SE = 0 
      IF  @CC = 1 
         SET @CC = 0
      ELSE
         SET @CC = 1;

   -- we are in a string, add the char in the output and loop again
   IF @CC = 1
   BEGIN
      SET @OUT = @OUT + @NC1
      SET @I = @I + 1;
      CONTINUE;
   END

   -- we are not in a string neither in a comment, and the next 2 chars are 
   -- not a comment's beginning. Add the char in the output and loop again
   IF @TT = 0 AND @SE = 0 AND @CC = 0 AND @NC2 NOT IN (N'--', '/*')
   BEGIN
      SET @OUT = @OUT + @NC1
      SET @I = @I + 1;
      CONTINUE;
   END

   -- we are not in "--" comment and the next 2 chars are a "/*" comment
   -- increments "/*" counter and pass 2 chars 
   IF @TT = 0 AND @NC2 = N'/*'
   BEGIN
      SELECT @SE = @SE + 1, 
             @I = @I + 2;
      CONTINUE;
   END;

   -- we are not in "/*" comment and the next 2 chars are a "--" comment
   -- set "--" bit to 1 and pass 2 chars 
   IF @TT = 0 AND @SE = 0 AND @NC2 = N'--'
   BEGIN
      SELECT @TT = 1, 
             @I = @I + 2;
      CONTINUE;
   END;

   -- we are in "/*" comment and the next 2 chars are a "*/" comment end
   -- decrease the "/*" counter and pass 2 chars
   IF @TT = 0 AND @SE > 0 AND @NC2 = N'*/'
   BEGIN
      SET @SE = @SE - 1;
      SET @I = @I + 2;
      CONTINUE;
   END;

   -- we are in "--" comment and the next 1 or 2 chars are a "--" comment end
   -- set "--" bit to 1 and pass 1 or 2 chars 
   IF @TT = 1 AND @SE = 0 AND @NC1 IN (NCHAR(13), NCHAR(10))
   BEGIN
      SET @TT = 0
      IF @NC2 = NCHAR(13) + NCHAR(10) 
         SET @I = @I + 2
      ELSE 
         SET @I = @I + 1
      CONTINUE;
   END;

   -- in all other conditions pass one char
   SET @I = @I + 1;

END;

-- clear the string for remaining ends of line or tab
SET @OUT = REPLACE(REPLACE(REPLACE(@OUT, NCHAR(9), ' '), 
                           NCHAR(10), ' '), 
                   NCHAR(13), ' ');


-- clear the string of duplicate spaces except in a string ('   ') or in a bracketed SQL identifier
DECLARE @KK BIT = 0, -- identifies if we are in a SQL identifier with brackets [...]
        @EE BIT = 0; -- identifies if we are in a string ' ... '
-- initializing
SELECT @SQL = @OUT, 
       @I = 0, 
       @CC = 0,
       @OUT = N'';
-- loop over the chars
WHILE @I <= LEN(@SQL)
BEGIN

   SET @I = @I + 1;
   SET @NC1 = SUBSTRING(@SQL, @I, 1);

   -- beginning of an identifier with brackets
   IF @NC1 = '[' AND @KK = 0 AND @CC = 0
   BEGIN
      SELECT @KK = 1, @EE = 0;
      SET @OUT = @OUT + @NC1
      CONTINUE;
   END;

   -- ending of an identifier with brackets
   IF @NC1 = ']' AND @KK = 1
   BEGIN
      SELECT @KK = 0, @EE = 0;
      SET @OUT = @OUT + @NC1
      CONTINUE;
   END;

   -- beginning of a string with simple quote
   IF @NC1 = '''' AND @KK = 0 AND @CC = 0
   BEGIN
      SELECT @CC = 1, @EE = 0;
      SET @OUT = @OUT + @NC1
      CONTINUE;
   END;

   -- ending of a string with simple quote
   IF @NC1 = '''' AND @CC = 1
   BEGIN
      SELECT @CC = 0, @EE = 0;
      SET @OUT = @OUT + @NC1
      CONTINUE;
   END;

   -- firts space we keep it always
   IF @NC1 = ' ' AND  @EE = 0
   BEGIN
      SET @OUT = @OUT + @NC1
      SET @EE = 1;
      CONTINUE
   END

   -- space following a space, in bracketed identifier or string
   IF @NC1 = ' ' AND  @EE = 1 AND (@KK = 1 OR @CC = 1)
   BEGIN
      SET @OUT = @OUT + @NC1
      CONTINUE
   END

   -- space following a space, outside bracketed identifier or string
   IF @NC1 = ' ' AND  @EE = 1 AND @KK = 0 AND @CC = 0
   BEGIN
      CONTINUE
   END

   -- no more spaces
   IF @NC1 <> ' ' AND  @EE = 1
      SET @EE = 0;

   SET @OUT = @OUT + @NC1;
END;

-- trim both the string
RETURN RTRIM(LTRIM(@OUT));

END
GO