Random String of Alpha character function

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Random String of Alpha character function

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-07-12 : 03:02:40

I need a function which should return Random String of Alpha character of particular length.
Let say I pass 3 as input it will return
[ABC] or [CDE] or [KOP] randomly

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-12 : 03:09:45

SELECT CHAR(65 + ABS(CHECKSUM(NEWID())) % 26)

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-12 : 04:18:11

Peso's solution in a function:


-- first we need a view on newid() because it is not possible
-- to use newid() inside a function
create view MyNewId as select newid() as New_Id

-- create the function
create function dbo.RandomString(@parmlen tinyint)
returns varchar(255)
as
begin
	declare @len tinyint
	set @len=@parmlen
	declare @return varchar(255)
	set @return = ''
	while len(@return) < @len
	begin
		SELECT @return = @return + CHAR(65 + ABS(CHECKSUM((select New_Id from MyNewId))) % 26)
	end
	return @return
end

-- test the function
select dbo.RandomString(3)