Remove All Text Between Single Quotes

There's a zillion posts and articles about how to escape single quotes in T-SQL, but what I wanted to do was strip out the characters that were inside the single quotes. After a ridiculous amount of Googling turned up nothing, I spent an even more ridiculous amount of time figuring it out.

The code below is a complete, working example of removing the characters from between two single quotes for every row of a table. It also removes the single quotes; modifying the code to leave the single quotes in is left as an exercise for the reader. (Ok, just add 1 to PosBeg and subtract 1 from PosEnd.)

--------------------------------------------
-- How to convert the rows' values to this: 
--                              
--      The cow says .          
--      The duck says .         
--      The clock says .        
--                              
-- In other words, remove the single quotes
-- and the characters *between* them. 
--------------------------------------------

SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE TABLE t (c NVARCHAR(4000) NOT NULL)

INSERT INTO t 
          SELECT 'The cow says ''moo''.'
    UNION SELECT 'The duck says ''quack''.'
    UNION SELECT 'The clock says ''eleven-thirty''.'
    
SELECT c FROM t ORDER BY c
 
UPDATE t SET c = STUFF(c,
                       CHARINDEX('''', c),
                       CHARINDEX('''', c, CHARINDEX('''', c) + 1) - CHARINDEX('''', c) + 1,
                       ''
                      )

SELECT c FROM t ORDER BY c
DROP TABLE t

If you insert the code below into the code above just before the UPDATE statement, it may make what's going on clearer:

SELECT CHARINDEX('''', c)                              AS PosBeg FROM t

SELECT CHARINDEX('''', c, CHARINDEX('''', c) + 1)      AS PosEnd FROM t
 
SELECT CHARINDEX('''', c, CHARINDEX('''', c) + 1) -
       CHARINDEX('''', c)                              AS Length FROM t 

SELECT SUBSTRING(c,
                 CHARINDEX('''', c),
                 CHARINDEX('''', c, CHARINDEX('''', c) + 1) - CHARINDEX('''', c) + 1
                )
  FROM t

The really important thing I learned from this is that (a) when people say T-SQL was not intended for string manipulation, they're not kidding, and (b) the way to do anything remotely fancy with strings is one tiny step at a time. As an old C/C++/C# developer, I underestimated the difficulty of this problem: playing with strings ceased to be interesting as soon as CString came out. I finally got it through my head that the only hope of implementing this in an intelligible way was to start from the inside, and work my way out. That's where the intermediate variables PosBeg, PosEnd, and Length came from - they were the baby steps I took along the way.

I haven't programmed using intermediate variables in a long, long time. I don't write C# like that, and I sure don't write other T-SQL that way, but string manipulation in T-SQL drove me back to it. I wonder if it's T-SQL's lack of powerful string operators, or just the familiarity of old habits, that are to blame?

Deleting User Objects in Master Database

This script erases everything in the master database that is not Microsoft-shipped. Be careful: some user objects belong in the master database (database triggers, security objects, for example). Backing up the master database first would be a prudent step.

-- Drops all user objects that have been accidentally created in the master database.
USE master

DECLARE @sName     sysname
DECLARE @sTypeDesc NVARCHAR(1024)
DECLARE @sType     NVARCHAR(1024)
DECLARE @nScore    INT
DECLARE @sSql      NVARCHAR(1024)

DECLARE TheCursor CURSOR FOR
 SELECT name, type_desc
   FROM sys.objects
  WHERE is_ms_shipped = 0

OPEN TheCursor

FETCH NEXT FROM TheCursor
 INTO @sName, @sTypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sType  = REVERSE(@sTypeDesc)
 SET @nScore = CHARINDEX('_', @sType)
 SET @sType  = SUBSTRING(@sType, 0, @nScore)
 SET @sType  = REVERSE(@sType)

 SET @sSql = 'DROP ' + @sType + ' ' + @sName
 RAISERROR('%s', 10, 1, @sSql) WITH NOWAIT 
 EXEC sp_sqlexec @sSql
 
 FETCH NEXT FROM TheCursor
  INTO @sName, @sTypeDesc
END

CLOSE TheCursor
DEALLOCATE TheCursor

PRINT 'Done.'