Generate a random string based on a number

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)

Generate a random string based on a number

Author

Topic

blued
Starting Member

9 Posts

Posted - 2011-09-22 : 03:02:39

Hi all,

I'm trying to create a function that will generate a random string of 5 characters long based on a 6 digit number.

For example

SELECT MyRandFunction(123456)
Returns PTHGY

SELECT MyRandFunction(654321)
Returns UYTHE

Next time I run the string generator I expect to see the same values for the same numbers I use. The reason is I need to re use the random string on a per number basis.

Has anyone done something similar using the RAND() function or similar?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 04:11:22

if its random how will return the same value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blued
Starting Member

9 Posts

Posted - 2011-09-25 : 03:28:10

Sorry I wasn't clear, it needs to generate a 5 character string from a 6 digit number. When I say random I just mean that it doesn't matter what the 5 characters are so long as it is unique to the 6 digit number.

If you see this thread - http://stackoverflow.com/questions/1324063/generating-random-strings-with-t-sql

The first reply is similar to what I need but I can't get it to work for a 5 character string from a 6 digit number.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-25 : 18:03:06

[code]DECLARE @Value INT = 654321

SELECT CHAR(65 + Value % 26) + Digit2 + Digit3 + Digit4 + Digit5
FROM (
SELECT Value / 26 AS Value,
CHAR(65 + Value % 26) AS Digit2,
Digit3,
Digit4,
Digit5
FROM (
SELECT Value / 26 AS Value,
CHAR(65 + Value % 26) AS Digit3,
Digit4,
Digit5
FROM (
SELECT Value / 26 AS Value,
CHAR(65 + Value % 26) AS Digit4,
Digit5
FROM (
SELECT @Value / 26 AS Value,
CHAR(65 + @Value % 26) AS Digit5
) AS d
) AS d
) AS d
) AS d[/code]

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

blued
Starting Member

9 Posts

Posted - 2011-09-25 : 19:41:28

Many thanks SwePeso, this is exactly what I was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources