SQL generates n-bit random strings and SQL generates n

SQL generates n-bit random strings and SQL generates n

-- 1. Use newid () go -- to create a view (because newid () cannot be directly used in the function) create view vnewid as select newid () N 'macoid '; go -- create function getrandstr (@ n int) returns varchar (max) as begin declare @ I int set @ I = ceiling (@ n/32.00) declare @ j int set @ j = 0 declare @ k varchar (max) set @ k = ''while @ j <@ I begin select @ k = @ k + replace (cast (MacoId as varchar (36 )),'-','') from vnewid set @ j = @ j + 1 end set @ k = substring (@ k, 1, @ n) return @ K end -- test example select dbo. getrandstr (75) -- run the result/* success */-- we can find that all the letters in the result are in upper or lower case. -- In another way, write down: go -- create function [dbo]. [m_rand] (@ mycount int) returns nvarchar (2000) as begin declare @ maco_wang table (id varchar (1) declare @ maco_number int, @ number int; declare @ my_one nvarchar (max), @ my_two nvarchar (max) set @ my_one = ''; set @ maco_number = 0; set @ number = 48; while (@ number> = 48 and @ number <= 57) or (@ number> = 65 and @ number <= 90) or (@ number> = 97 and @ number <= 122) begin insert into @ maco_wang select char (@ number) set @ number = @ number + 1; if (@ number = 58) begin set @ number = 65 end if (@ number = 91) begin set @ number = 97 end while @ maco_number <@ mycount begin select @ my_two = id from @ maco_wang order by (select MacoId from dbo. m_macoview); set @ my_one = @ my_two + @ my_one; set @ maco_number = @ maco_number + 1; end return @ my_one end -- Test Case select [dbo]. [m_rand] (75) -- running result/* running */

Several Methods for randomly generating strings in SQL

1. uniqueidentifier generated using newid () is random and unique: declare @ string nvarchar (100); set @ string = cast (newid () as nvarchar (100 )); select @ string; go2. use rand () to generate a random number string: declare @ string nvarchar (100); set @ string = right (str (rand (),), 2 ); select @ string; go3. use rand () to generate six random strings: declare @ SQL nvarchar (400) select @ SQL = 'select char ('+ cONVERT (NVARCHAR, CONVERT (INT, 26 * rand () + 97) + ') + CHAR (' + CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) + ') + CHAR ('+ CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) +') + CHAR ('+ CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) + ') + CHAR (' + CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) + ') + CHAR ('+ CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) +') 'print @ SQL EXEC (@ SQL) goDECLARE @ Below int DECLARE @ Up int SELECT @ Below = 65, @ Up = 90 select char (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0) + CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0 ))) + CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0) + CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0) + CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0) + CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0 ))) goSELECT CHAR (CONVERT (INT, rand () * 26) + (case when rand () * 2> 1 THEN 97 ELSE 65 END) + CHAR (CONVERT (INT, rand () * 26) + (case when rand () * 2> 1 THEN 97 ELSE 65 END) + CHAR (CONVERT (INT, rand () * 26) + (case when rand () * 2> 1 THEN 97 ELSE 65 END) + CHAR (CONVERT (INT, rand () * 26) + (case when rand () * 2> 1 THEN 97 ELSE 65 END) + CHAR (CONVERT (INT, rand () * 26) + (case when rand () * 2 ...... remaining full text>

SQL2000, how to random string

1. uniqueidentifier generated by using newid () is random and unique:
Declare @ string nvarchar (100 );
Set @ string = cast (newid () as nvarchar (100 ));
Select @ string;
Go
2. Use rand () to generate a random number string:
Declare @ string nvarchar (100 );
Set @ string = right (str (rand (), 8, 6), 2 );
Select @ string;
Go

3. Use rand () to generate 6 random strings:
Declare @ SQL nvarchar (400)
Select @ SQL = 'select char ('+ cONVERT (NVARCHAR, CONVERT (INT, 26 * rand () + 97) +') +
CHAR ('+ CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) +') +
CHAR ('+ CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) +') +
CHAR ('+ CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) +') +
CHAR ('+ CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) +') +
CHAR ('+ CONVERT (NVARCHAR, CONVERT (INT, rand () * 26) + 97) + ')'
Print @ SQL
EXEC (@ SQL)
Go

DECLARE @ Below int
DECLARE @ Up int
SELECT @ Below = 65, @ Up = 90
Select char (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0 )))
+ CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0 )))
+ CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0 )))
+ CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0 )))
+ CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0 )))
+ CHAR (CAST (RAND () * (@ Up-@ Below) + @ Below AS decimal (38,0 )))

Go
Select char (CONVERT (INT, rand () * 26) + (case when rand () * 2> 1 THEN 97 ELSE 65 END) +
CHAR (CONVERT (INT, rand () * 26) + (case when rand () * 2> 1 THEN 97 ELSE 65 END) +
CHAR (CONVERT (INT, rand () * 26) + (case when rand () * 2 & gt ...... remaining full text>