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?

ASCII Bar Chart of Job History

If you're like me, it's a lot easier to understand things with a picture. With that in mind, here's a script you can run as a Job which will print out a visual representation of the Jobs that ran in the last 24 hours. The results are emailed to whatever address you provide (near the end of the script).

-------------------------------------------------------------------------------
-- Prints a primitive bar chart that shows all the jobs that ran during the    
-- last 24 hours.  Useful for visualizing overlap.                             
--                                                                             
-- 2010-08-17 - Larry Leonard                                                  
-------------------------------------------------------------------------------

USE YourDatabase
SET STATISTICS IO OFF
SET NOCOUNT ON
GO

-- This is the only variable you can set.  It is the width you want the 
-- output to be, in characters.
DECLARE @nScreenDotWidth INT    SET @nScreenDotWidth = 80


-------------------------------------------------------------------------------
-- Create the temp table that will hold all the Job History data.              
-------------------------------------------------------------------------------

;WITH cte AS
(
SELECT jv.name          AS 'sJobName',
       jv.description   AS 'sJobDescription',

       SUBSTRING(CAST(      jh.run_date     AS CHAR(8)), 5, 2) + '/' +
                 CAST(RIGHT(jh.run_date, 2) AS CHAR(2))        + '/' +
                 CAST(LEFT (jh.run_date, 4) AS CHAR(4))                               AS 'sRunStartDate', 

       CAST(LEFT(RIGHT(STUFF(jh.run_time, 1, 0, '0'), 6), 2) AS CHAR(2)) + ':' + 
       CAST(LEFT(RIGHT(      jh.run_time, 4), 2)             AS CHAR(2)) + ':' +
       CAST(RIGHT(           jh.run_time, 2)                 AS CHAR(2))              AS 'sRunStartTime',

       ((60 * 60 * CAST(     LEFT(STUFF(jh.Run_Duration, 1, 0, REPLICATE('0', 6 - LEN(jh.Run_Duration))), 2)    AS INT)) +  
        (     60 * CAST(SUBSTRING(STUFF(jh.Run_Duration, 1, 0, REPLICATE('0', 6 - LEN(jh.Run_Duration))), 3, 2) AS INT)) +  
        (          CAST(    RIGHT(STUFF(jh.Run_Duration, 1, 0, REPLICATE('0', 6 - LEN(jh.Run_Duration))), 2)    AS INT)) 
       ) / 60                                                                         AS 'nRunDurationMins'

  FROM msdb.dbo.sysjobs_view    jv
  JOIN msdb.dbo.sysjobhistory   jh
    ON jv.job_id = jh.job_id
 WHERE jh.step_id = 0                               -- "Job is completed" step.
)   
 SELECT   sJobName                               AS 'sJobName'
        , sRunStartDate + ' ' + sRunStartTime    AS 'sRunStart'
        , nRunDurationMins                       AS 'nRunDurationMins'
   INTO #Chart
   FROM cte


-------------------------------------------------------------------------------
-- Add the runs' start and stop time from the ridiculous format returned by    
-- sysjobhistory.  Use this info to only look at last 24 hours.                
-------------------------------------------------------------------------------

ALTER TABLE #Chart ADD dtRunStart DATETIME NULL
ALTER TABLE #Chart ADD dtRunEnd   DATETIME NULL

UPDATE #Chart
   SET dtRunStart = CAST(sRunStart AS DATETIME)

UPDATE #Chart
   SET dtRunEnd   = DATEADD(MINUTE, nRunDurationMins, dtRunStart)

DELETE FROM #Chart
 WHERE dtRunEnd < DATEADD(HOUR, -24, GETDATE())
    OR nRunDurationMins = 0  
  

-------------------------------------------------------------------------------
-- Calculate each job run's start and stop points on the chart.                
-------------------------------------------------------------------------------

DECLARE @dtLatest    DATETIME    SELECT @dtLatest    = MAX(dtRunEnd) FROM #Chart
DECLARE @dtEarliest  DATETIME    SELECT @dtEarliest  = DATEADD(hour, -24, @dtLatest)
DECLARE @nMinsPerDot INT         SET    @nMinsPerDot = DATEDIFF(MINUTE, @dtEarliest, @dtLatest) / @nScreenDotWidth

ALTER TABLE #Chart ADD StartDot INT NULL
ALTER TABLE #Chart ADD EndDot   INT NULL

UPDATE #Chart
   SET StartDot = DATEDIFF(MINUTE, @dtEarliest, dtRunStart) / @nMinsPerDot

UPDATE #Chart
   SET EndDot   = DATEDIFF(MINUTE, @dtEarliest, dtRunEnd)   / @nMinsPerDot


-------------------------------------------------------------------------------
-- Iterate through the job runs, creating a line for each one.                 
-------------------------------------------------------------------------------
   
DECLARE @nMaxJobNameLen INT             SELECT @nMaxJobNameLen = MAX(LEN(sJobName)) FROM #Chart
DECLARE @sJobName       NVARCHAR(4000)
DECLARE @StartDot       INT              
DECLARE @EndDot         INT 
DECLARE @sChart         NVARCHAR(MAX)   SET @sChart = ''
DECLARE @sLine          NVARCHAR(4000)

DECLARE cur CURSOR FOR
    SELECT sJobName, StartDot, EndDot
      FROM #Chart
  ORDER BY StartDot,
           EndDot DESC
   
OPEN cur
FETCH cur INTO @sJobName, @StartDot, @EndDot 

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sLine =    
                @sJobName
              + REPLICATE(' ', @nMaxJobNameLen - LEN(@sJobName))
              + ' !'
              + REPLICATE(' ', @StartDot) 
              + '|' 
              + CASE 
                     WHEN @EndDot - @StartDot - LEN('||') > 0
                     THEN REPLICATE('-', @EndDot - @StartDot - LEN('||')) 
                     ELSE '-'
                END
              + '|'

    SET @sChart = @sChart + CHAR(10) + @sLine
    FETCH cur INTO @sJobName, @StartDot, @EndDot 
END

CLOSE cur
DEALLOCATE cur

DROP TABLE #Chart


-------------------------------------------------------------------------------
-- Add top and bottom borders, and the date range, to the chart.               
-------------------------------------------------------------------------------
 
SET @sChart = REPLICATE('=', LEN(@sLine) + 1) + @sChart

SET @sLine = CAST(@dtEarliest AS NVARCHAR) 
           + REPLICATE(' ', LEN(@SLine) - (2 * LEN('Aug 16 2010  5:05PM')) + 1)
           + CAST(@dtLatest AS NVARCHAR)

SET @sChart = @sChart + char(10) + REPLICATE('=', LEN(@sLine) + 1) + char(10) + @sLine


----------------------------------------------------------------------------------
-- "PRINT" truncates NVARCHAR(MAX) variables at 8000 bytes.  So we do this.       
----------------------------------------------------------------------------------

DECLARE @CurrentEnd  BIGINT             -- Track the length of the next substring.
DECLARE @offset      TINYINT            -- Tracks the amount of offset needed.
DECLARE @sChartPiece NVARCHAR(MAX)

SET @sChartPiece = REPLACE(REPLACE(@sChart, CHAR(13) + CHAR(10), CHAR(10)), CHAR(13), CHAR(10))

WHILE LEN(@sChartPiece) > 1
BEGIN
    IF CHARINDEX(CHAR(10), @sChartPiece) BETWEEN 1 AND 4000
    BEGIN
        SET @CurrentEnd =  CHARINDEX(CHAR(10), @sChartPiece) - 1
        SET @offset = 2
    END
    ELSE
    BEGIN
        SET @CurrentEnd = 4000
        SET @offset = 1
    END

    PRINT SUBSTRING(@sChartPiece, 1, @CurrentEnd)
    SET @sChartPiece = SUBSTRING(@sChartPiece, @CurrentEnd+@offset, 1073741822)  -- 0x3FFFFFF 
END


-------------------------------------------------------------------------------
-- Send me an email.                                                           
-------------------------------------------------------------------------------

DECLARE @EmailImportance NVARCHAR(6)      SET @EmailImportance = 'Normal'
DECLARE @EMailSubject    NVARCHAR(255)    SET @EMailSubject    = 'Daily Job History Chart'
DECLARE @EmailBody       NVARCHAR(MAX)    SET @EmailBody       = @sChart
DECLARE @EmailItemID     INT              SET @EmailItemID     = 0
DECLARE @ProfileId       INT              SET @ProfileId       = -1
DECLARE @ProfileName     sysname          SET @ProfileName     = 'YourProfile'
DECLARE @nErr            INT              SET @nErr            = @@ERROR

EXEC msdb.dbo.sp_send_dbmail
    @recipients      = 'president@whitehouse.gov',
    @body            = @EmailBody,
    @body_format     = 'TEXT',
    @subject         = @EMailSubject,
    @importance      = @EmailImportance,
    @profile_name    = @ProfileName,
    @mailitem_id     = @EmailItemID OUTPUT

-- Check the results of trying to send the mail.
SET @nErr = @@ERROR

IF @EmailItemID != 0
BEGIN
    RAISERROR ('sp_send_dbmail - @EmailItemID %d', 10, 1, @EmailItemID) WITH NOWAIT, LOG
END

IF @nErr != 0
BEGIN
    RAISERROR ('sp_send_dbmail - @@ERROR %d', 16, 1, @nErr) WITH NOWAIT, LOG
END

Here's some example results. (This data is entirely made up - I don't really rebuild indexes that often.) Primitive to be sure, but still makes it easy to spot five jobs running at the same time!

==================================================================================================
CHECKDB         ! |--|
Reorg Indexes   !  |-|
CHECKDB         !            |---|
Reorg Indexes   !             |-|
CHECKDB         !                 |---|
Reorg Indexes   !                   |-|
Reorg Indexes   !                   |-|
Rebuild Indexes !                    |-----------------------------------------------------------|
CHECKDB         !                       |--|
Reorg Indexes   !                        |-|
CHECKDB         !                            |---|
Reorg Indexes   !                              |-|
CHECKDB         !                                  |--|
Reorg Indexes   !                                   |-|
CHECKDB         !                                      |------|
CHECKDB         !                                      |--|
Reorg Indexes   !                                        |----|
Daily Reboot    !                                           |--|
CHECKDB         !                                            |--------------|
Reorg Indexes   !                                             |-|
CHECKDB         !                                                 |---|
Reorg Indexes   !                                                  |-|
CHECKDB         !                                                       |---|
Reorg Indexes   !                                                        |-|
Reorg Indexes   !                                                             |-|
Load AR data    !                                                              |-|
CHECKDB         !                                                                 |-----|
Reorg Indexes   !                                                                   |-|
Run Reports     !                                                                   |-|
Load AR data    !                                                                     |-|
Reorg Indexes   !                                                                        |-|
CHECKDB         !                                                                            |---|
Reorg Indexes   !                                                                              |-|
==================================================================================================
Aug 16 2010 10:49PM                                                            Aug 17 2010 10:49PM

Scriplet: Display Connections Per User & Database

A quick script to list out which users have how many connections with which databases.

-- Display users' connections per database.
SELECT DB_NAME(dbid) AS 'Database Name', 
       COUNT(dbid)   AS 'No Of Connections',
       loginame      AS 'Login Name'
  FROM sys.sysprocesses
 WHERE dbid > 0
 GROUP BY dbid, loginame