1

I want to generate a random id that uses table first and last name and a random number at the end of the login. I am not able to figure out how to use RAND function to add single random number at the end of name. Please help me. It will be appreciated. Thanks

DECLARE CUSTOMER_ID_CURSOR CURSOR 
STATIC 
FOR 
SELECT [first_name]+[last_name] as [ADMINISTRATOR ID] from administrators ;
OPEN CUSTOMER_ID_CURSOR;
FETCH NEXT FROM CUSTOMER_ID_CURSOR;
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM CUSTOMER_ID_CURSOR;
CLOSE CUSTOMER_ID_CURSOR
DEALLOCATE CUSTOMER_ID_CURSOR
ElGavilan
  • 6,610
  • 16
  • 27
  • 36
Usman Shafi Arain
  • 83
  • 1
  • 3
  • 12

2 Answers2

2

The RAND() function is unsuitable for your purpose as it always produces the same number in a batch unless every row gets a new seed. Use the following:

SELECT [first_name]+[last_name] + 
  CAST(CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) AS CHAR(1))
  as [ADMINISTRATOR ID] from administrators ;

as your SELECT. It uses NEWID() to produce an UNIQUEIDENTIFIER for each row which is used as a seed for the RAND() function. The rest of the expression gets an integer between 1 and 10 and CASTs it as a CHAR(1) before adding it to your string.

JohnS
  • 1,942
  • 1
  • 13
  • 16
0

you can achieve your goal through below query.

SELECT [first_name]+[last_name]
+cast (ROW_NUMBER() over(order by first_name ) as varchar(100))as [ADMINISTRATOR ID] 
from administrators ;
user3864233
  • 514
  • 3
  • 12