T-SQL's REPLACE() Function

DECLARE @VAR AS VARCHAR(50)
SET @VAR = 'DEF LEPPARD IS THE GREATEST ROCK BAND EVER!'
SELECT @VAR
-- USE W/ SET
SET @VAR = REPLACE(@VAR, 'DEF LEPPARD', 'METALLICA')
SELECT @VAR
-- USE W/ SELECT
SELECT REPLACE(@VAR,'METALLICA','LED ZEPPELIN')

-- LOOKS LIKE REPLACE() CAN ONLY BE USED IN EITHER A
-- SELECT STATEMENT OR WHEN SETTING A VARIABLE
-- http://msdn.microsoft.com/en-us/library/ms186862.aspx

Escaping or Inserting Single Quotes into a String in T-SQL

Just in case anyone ever needs to do this. This gave me a headache for about 30 minutes this morning:


DECLARE @MYVAR AS VARCHAR(100)
DECLARE @ESCAPE AS VARCHAR(100)
SET @MYVAR = 'PLACING SINGLE QUOTES INTO A STRING'
SET @ESCAPE = 'Single quote''s are escaped with double single quotes like these: '''''
SELECT @MYVAR AS MYVAR
SET @MYVAR = '''' + @MYVAR + ''''
SELECT @MYVAR AS MYVAR
SELECT @ESCAPE AS VAR

Adding a Constraint to a SQL Server DB field to ensure that the field contains a particular string

  This was an interesting problem. What if, for some strange reason, you wanted to make sure that a field contained a certain string or it didn't get inserted? This can be handled pretty easily on the front end, but just for fun, let's try doing it on the back end:

-- Unrealistic, I'm sure. Let's just say that we ONLY want people
-- with Gmail email addresses adding their emails into our table.

ALTER TABLE myTable
WITH CHECK ADD
CONSTRAINT [ValidEmail]
CHECK
(
Email LIKE '%@gmail.com'
)
GO

Randomly Order a Result Set in SQL Server

SELECT *
FROM myTable
ORDER BY NEWID()

SQL Server sp for Starting a Job

EXEC msdb.dbo.sp_start_job 'My_Job'

Query to List Recovery Model for all DBs on a SQL Server

SELECT DBID, [name],databasepropertyex([name],'Recovery') as [Recovery]
FROM master.dbo.sysdatabases
ORDER BY [name];