DZone Snippets: tsql code

PRNG In SQL Select

jokeyxero (xero) — Thu, 17 May 2007 00:39:32 GMT

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

TSQL overwrite whole table column with the value of one row in the column

simbo1905 () — Thu, 06 Apr 2006 20:12:37 GMT

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?

DBMAIL

codeguy (Sean Harvell) — Sat, 25 Mar 2006 18:39:50 GMT

// 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

codeguy (Sean Harvell) — Sat, 25 Mar 2006 18:26:18 GMT

// simple raise error



IF UPDATE(lastname)

BEGIN

	RAISERROR ('cannot change lastname (source = instead of)', 16, 1)

	ROLLBACK TRAN

	RETURN

END

SQL Server 2000 RegEx

codeguy (Sean Harvell) — Sat, 25 Mar 2006 17:09:42 GMT

// 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