Simple example of a PRNG (pseudo-random number generator) written into a SQL statement

Example is in T-SQL, but it ports well

Actual application should use either a better random algorithm, or the output be used with randomized seeds. This is definitely not cryptographically secure. It's very handy if you need a simple random number with your recordset though.

-- Setup some vars we'll need
DECLARE @prng TABLE (seed BIGINT, rnum nchar(10))
DECLARE @seeds TABLE (seed BIGINT)
DECLARE @seed BIGINT
DECLARE @C1 BIGINT, @C2 BIGINT, @C3 BIGINT
SET @seed = 0
SET @C1 = 1664525
SET @C2 = 4294967296
SET @C3 = 1013904223

-- Create a seed table so we can have some data to use
WHILE @seed < 10
BEGIN
    INSERT INTO @seeds (seed) VALUES (@seed)
    SET @seed = @seed + 1
END

-- Create our PRNG (inserts into table for illustrative purposes)
-- prng(seed) ::= ((((C1 * seed) % C2) + C3) % C2) / C2
-- Then convert prng(seed) into a string
-- of 10 chars, 8 of which are decimal places
INSERT INTO @prng
SELECT
    seed,
    REPLACE(
        STR(
            ( CAST((((@C1*seed)%@C2)+@C3)%@C2 AS FLOAT) )
            / ( CAST(@C2 AS FLOAT)),
            10, 8
        ),
        ' ', '0') AS rnum
FROM @seeds

-- Let's take a look at what we created
SELECT * FROM @prng

You want to remove some sensitive data from a column in a table in a test database. Just to make things hard the secret data within the column are massive encoded blobs. Large blobs dont work in subqueries nor the standard datbase tools - you would have to extracting it to disk. So you are looking for an 'update from select' query do solve the problem.

You have added one new row into the table that has test data in the sensitive column. You now want a query to write the sensitive column in this new row over the rest of the column in the table. The following code does the job:

update SECRET_TABLE set SECRET_COLUMN = SOURCE_TABLE.NOT_SECRET from SECRET_TABLE st1 inner join (
select st2.[ID] as [ignore], st3.[ID], st2.[SECRET_COLUMN] as [NOT_SECRET] from SECRET_TABLE st2 cross join SECRET_TABLE st3 where st2.[ID] = 'not_secret_row_id'
) as SOURCE_TABLE on SOURCE_TABLE.[ID] = st1.[ID]

it is a bit slow due to the cross join but as it was an infrequent task it was not a problem for me. any hints on doing it without the cross join?

// sql server 2005 sample dbmail send

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'dbmailprofile', @recipients = 'me@co.com,you@cocom', @body = 'the body', @subject = 'the subject'

// simple raise error

IF UPDATE(lastname)
BEGIN
	RAISERROR ('cannot change lastname (source = instead of)', 16, 1)
	ROLLBACK TRAN
	RETURN
END

// SQL 2K RegEx Compat Code

CREATE FUNCTION dbo.find_regular_expression
	(
		@source varchar(5000),
		@regexp varchar(1000),
		@ignorecase bit = 0
	)
RETURNS bit
AS
	BEGIN
		DECLARE @hr integer
		DECLARE @objRegExp integer
		DECLARE @objMatches integer
		DECLARE @objMatch integer
		DECLARE @count integer
		DECLARE @results bit
		
		EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
		EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
		EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
		EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
			
		EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
		EXEC @hr = sp_OADestroy @objRegExp
		IF @hr <> 0 BEGIN
			SET @results = 0
			RETURN @results
		END
	RETURN @results
	END