Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-09-29 : 08:02:54
|
Hey,I'm wondering if anybody has a function that will create a random string. Basically I want to update a column in my table with a random array of characters.I don't need any high security mandatory letter and number combinations, but I'll take anything I can get.much appreciated, thanks! :)mike123 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-29 : 08:05:20
|
UPDATE Table1SET Col1 = LEFT(REPLACE(CAST(NEWID() AS CHAR(40)), '-', ''), @Characters) E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-29 : 08:07:08
|
For a five-digit password starting with 1UPDATE Table1SET Col1 = 10000 + ABS(CHECKSUM(NEWID())) % 90000 E 12°55'05.63"N 56°04'39.26" |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-09-29 : 08:07:53
|
quote: Originally posted by Peso UPDATE Table1SET Col1 = LEFT(REPLACE(CAST(NEWID() AS CHAR(40)), '-', ''), @Characters) E 12°55'05.63"N 56°04'39.26"
Hey Peso,We are on a roll here! this is perfect yet again...thanks so much :)mike123 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-09-29 : 08:08:30
|
quote: Originally posted by Peso For a five-digit password starting with 1UPDATE Table1SET Col1 = 10000 + ABS(CHECKSUM(NEWID())) % 90000 E 12°55'05.63"N 56°04'39.26"
even better again, thanks!! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-29 : 08:20:26
|
This is a stored procedure that can create a complex password for you.A negative parameter denotes a unique digit of that charactergroup, ie no "gg" for exampleCREATE PROCEDURE dbo.uspCreatePassword( @UpperCaseItems SMALLINT, @LowerCaseItems SMALLINT, @NumberItems SMALLINT, @SpecialItems SMALLINT)ASSET NOCOUNT ON-- Initialize some variablesDECLARE @UpperCase VARCHAR(26), @LowerCase VARCHAR(26), @Numbers VARCHAR(10), @Special VARCHAR(13), @Temp VARCHAR(8000), @Password VARCHAR(8000), @i SMALLINT, @c VARCHAR(1), @v TINYINT -- Set the default items in each group of charactersSELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', @LowerCase = 'abcdefghijklmnopqrstuvwxyz', @Numbers = '0123456789', @Special = '!@#$%&*()_+-=', @Temp = '', @Password = '' -- Enforce some limits on the length of the passwordIF @UpperCaseItems > 20 SET @UpperCaseItems = 20IF @UpperCaseItems < -20 SET @UpperCaseItems = -20IF @LowerCaseItems > 20 SET @LowerCaseItems = 20IF @LowerCaseItems < -20 SET @LowerCaseItems = -20IF @NumberItems > 20 SET @NumberItems = 20IF @NumberItems < -20 SET @NumberItems = -20IF @SpecialItems > 20 SET @SpecialItems = 20IF @SpecialItems < -20 SET @SpecialItems = -20-- Get the Upper Case ItemsSET @i = ABS(@UpperCaseItems) WHILE @i > 0 AND LEN(@UpperCase) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1, @c = SUBSTRING(@UpperCase, @v, 1), @UpperCase = CASE WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '') ELSE @UpperCase END, @Temp = @Temp + @c, @i = @i - 1-- Get the Lower Case ItemsSET @i = ABS(@LowerCaseItems) WHILE @i > 0 AND LEN(@LowerCase) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1, @c = SUBSTRING(@LowerCase, @v, 1), @LowerCase = CASE WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '') ELSE @LowerCase END, @Temp = @Temp + @c, @i = @i - 1 -- Get the Number ItemsSET @i = ABS(@NumberItems) WHILE @i > 0 AND LEN(@Numbers) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1, @c = SUBSTRING(@Numbers, @v, 1), @Numbers = CASE WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '') ELSE @Numbers END, @Temp = @Temp + @c, @i = @i - 1 -- Get the Special ItemsSET @i = ABS(@SpecialItems) WHILE @i > 0 AND LEN(@Special) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1, @c = SUBSTRING(@Special, @v, 1), @Special = CASE WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '') ELSE @Special END, @Temp = @Temp + @c, @i = @i - 1 -- Scramble the order of the selected itemsWHILE LEN(@Temp) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1, @Password = @Password + SUBSTRING(@Temp, @v, 1), @Temp = STUFF(@Temp, @v, 1, '') SELECT @Password E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-29 : 08:30:19
|
And if you want it as a function, you will have to "cheat" and make a view first, like thisCREATE VIEW dbo.vwNEWIDASSELECT ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) AS nwd A´nd with this view in place, you can write your function like thisCREATE FUNCTION dbo.fnCreatePassword( @UpperCaseItems SMALLINT, @LowerCaseItems SMALLINT, @NumberItems SMALLINT, @SpecialItems SMALLINT)RETURNS VARCHAR(80)ASBEGIN -- Initialize some variables DECLARE @UpperCase VARCHAR(26), @LowerCase VARCHAR(26), @Numbers VARCHAR(10), @Special VARCHAR(13), @Temp VARCHAR(8000), @Password VARCHAR(8000), @i SMALLINT, @c VARCHAR(1), @v TINYINT -- Set the default items in each group of characters SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', @LowerCase = 'abcdefghijklmnopqrstuvwxyz', @Numbers = '0123456789', @Special = '!@#$%&*()_+-=', @Temp = '', @Password = '' -- Enforce some limits on the length of the password IF @UpperCaseItems > 20 SET @UpperCaseItems = 20 IF @UpperCaseItems < -20 SET @UpperCaseItems = -20 IF @LowerCaseItems > 20 SET @LowerCaseItems = 20 IF @LowerCaseItems < -20 SET @LowerCaseItems = -20 IF @NumberItems > 20 SET @NumberItems = 20 IF @NumberItems < -20 SET @NumberItems = -20 IF @SpecialItems > 20 SET @SpecialItems = 20 IF @SpecialItems < -20 SET @SpecialItems = -20 -- Get the Upper Case Items SET @i = ABS(@UpperCaseItems) WHILE @i > 0 AND LEN(@UpperCase) > 0 SELECT @v = nwd % LEN(@UpperCase) + 1, @c = SUBSTRING(@UpperCase, @v, 1), @UpperCase = CASE WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '') ELSE @UpperCase END, @Temp = @Temp + @c, @i = @i - 1 FROM dbo.vwNEWID -- Get the Lower Case Items SET @i = ABS(@LowerCaseItems) WHILE @i > 0 AND LEN(@LowerCase) > 0 SELECT @v = nwd % LEN(@LowerCase) + 1, @c = SUBSTRING(@LowerCase, @v, 1), @LowerCase = CASE WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '') ELSE @LowerCase END, @Temp = @Temp + @c, @i = @i - 1 FROM dbo.vwNEWID -- Get the Number Items SET @i = ABS(@NumberItems) WHILE @i > 0 AND LEN(@Numbers) > 0 SELECT @v = nwd % LEN(@Numbers) + 1, @c = SUBSTRING(@Numbers, @v, 1), @Numbers = CASE WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '') ELSE @Numbers END, @Temp = @Temp + @c, @i = @i - 1 FROM dbo.vwNEWID -- Get the Special Items SET @i = ABS(@SpecialItems) WHILE @i > 0 AND LEN(@Special) > 0 SELECT @v = nwd % LEN(@Special) + 1, @c = SUBSTRING(@Special, @v, 1), @Special = CASE WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '') ELSE @Special END, @Temp = @Temp + @c, @i = @i - 1 FROM dbo.vwNEWID -- Scramble the order of the selected items WHILE LEN(@Temp) > 0 SELECT @v = nwd % LEN(@Temp) + 1, @Password = @Password + SUBSTRING(@Temp, @v, 1), @Temp = STUFF(@Temp, @v, 1, '') FROM dbo.vwNEWID RETURN @PasswordEND And all you now have to do is to query the function with something like thisSELECT dbo.fnCreatePassword(2, 2, -2, 0) meaningAny 2 UpperCase charactersAny 2 LowerCase charactersAny 2 unique digitsNo special characters E 12°55'05.63"N 56°04'39.26" |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-09-29 : 08:32:41
|
wow this procedure is above and beyond what I expected.. works perfectly, my perfect solution is even better now :Dthanks again peso !! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-09-29 : 09:10:16
|
Hey Peso,Very very thorough answer, I evaluated all the options and I think your last suggestion is just perfect.I am wondering tho, how to update the tables column? For example:update users set PW2 = (exec uspCreatePassword 0,3,3,0)not exactly sure how to implement this, if you dont mind helping me out on this final step, that would be awesome!Thanks again,mike123 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-29 : 09:44:28
|
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspxMadhivananFailing to plan is Planning to fail |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-09-29 : 10:01:10
|
quote: Originally posted by madhivanan http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspxMadhivananFailing to plan is Planning to fail
Hi madhivanan,Appreciate the input, is this what I want to do tho ? I am looking to update a column with the single result brought back by this SPROC.I am not looking to filter the results.would this sproc be more functional to me as a function ? (just a guess)thanks again,mike123 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-29 : 10:05:35
|
Something likeupdate users set PW2 = (Select *from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','Execute yourdb..exec uspCreatePassword 0,3,3,0')) Provided procedure returns single valueMadhivananFailing to plan is Planning to fail |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-09-29 : 10:41:00
|
Hi Madhivanan,Yes it does return a single value. This routine looks like it will work. Just wondering tho, this SPROC, for this particular application, would it be better as a function ? Is there an easier/cleaner way to accomplish this update?Thanks again!mike123 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-29 : 10:49:00
|
See post made 09/29/2008 : 08:30:19 E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|