How to Generate Alphanumeric Random numbers using function in SQL Server 2008

Question

I need to generate alphanumeric random number with 6 character length and it should contain Numerics, Alphabets (Both Lowercase and Uppercase) check the query below.

I NEED TO IMPLEMENT IN FUNCTION. (In function is it possible to use NEWID(), RAND()).

SELECT SUBSTRING(CONVERT(VARCHAR(255), NEWID()),0,7)

Output:

23647D
06ABA9
542191
.
.
.

I Need Output as:

236m7D
3n64iD
6t4M7D
.
.
.

Answer 1

As in function we cannot use NEWID() OR RAND() first need to create VIEW

For Function

CREATE VIEW NewID as select newid() as new_id


DECLARE @new_id VARCHAR(255)


SELECT @new_id = new_id FROM newid


SELECT @Password = CAST((ABS(CHECKSUM(@new_id))%10) AS VARCHAR(1)) + 
CHAR(ASCII('a')+(ABS(CHECKSUM(@new_id))%25)) +
CHAR(ASCII('A')+(ABS(CHECKSUM(@new_id))%25)) +
LEFT(@new_id,3)


SELECT @PASSWORD

Output:

9eEF44
5uUFA2
7hHFA7
.
.
.

For Select Statement

DECLARE @new_id VARCHAR(200)

SELECT @new_id = NEWID()

SELECT CAST((ABS(CHECKSUM(@new_id))%10) AS VARCHAR(1)) + 
CHAR(ASCII('a')+(ABS(CHECKSUM(@new_id))%25)) +
CHAR(ASCII('A')+(ABS(CHECKSUM(@new_id))%25)) +
LEFT(@new_id,3)

Output:

0aAF3C
5pP3CE
2wW85E
.
.
.

Answer 2

Try this:

 select cast((Abs(Checksum(NewId()))%10) as varchar(1)) + 
       char(ascii('a')+(Abs(Checksum(NewId()))%25)) +
       char(ascii('A')+(Abs(Checksum(NewId()))%25)) +
       left(newid(),5) Random_Number

Also,

    DECLARE @exclude varchar(50) 
    SET @exclude = '0:;<=>?@O[]`^\/'
    DECLARE @char char
    DECLARE @len char
    DECLARE @output varchar(50)
    set @output = ''
    set @len = 8

    while @len > 0 begin
       select @char = char(round(rand() * 74 + 48, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output = @output + @char
           set @len = @len - 1
       end
    end

   SELECT @output

can be used.