How can I generate a random string? I wrote the following, which gave my only one letter as a result !
declare @alphaCount int
set @alphaCount = @alphaCount +1
CHAR(@alphaCount)
Thanks in advance!
-
2SQL is a domain specific query langauge, not a full fledged programming language. generate random string on the client, not on the db side.Davita– Davita2010-12-29 22:30:00 +00:00Commented Dec 29, 2010 at 22:30
-
I want to fill my dataBase with data!!Lisa– Lisa2010-12-29 22:32:19 +00:00Commented Dec 29, 2010 at 22:32
-
What type of random string do you need? (length and acceptable characters)Martin Smith– Martin Smith2010-12-29 22:35:01 +00:00Commented Dec 29, 2010 at 22:35
-
length 20, acceptable characters A-Z a-zLisa– Lisa2010-12-29 22:38:39 +00:00Commented Dec 29, 2010 at 22:38
Add a comment
|
4 Answers
This will give you 10,000 rows in the specified format.
DECLARE @Numbers TABLE
(
n INT PRIMARY KEY
);
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1), --2
E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --256
E16(N) AS (SELECT 1 FROM E08 a, E08 b) --65,536
INSERT INTO @Numbers
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM E16
SELECT CAST((SELECT TOP 20 CHAR(CASE
WHEN Abs(Checksum(Newid()))%2 = 0 THEN 65
ELSE 97
END + Abs(Checksum(Newid()))%26)
FROM @Numbers n1
WHERE n1.n >= -n2.n /*So it gets re-evaluated for each row!*/
FOR XML PATH('')) AS CHAR(20))
FROM @Numbers n2
Comments
Fill a temporary table with a list of random words. Then use CROSS JOIN to combine every word with every other word, to get a whole ton of data with little effort.
select l.word+' '+r.word from #Words as l
cross join #Words as r
2 Comments
Lisa
How can I generate list of random words?
Mark Ransom
@Shaza, just make some up! The cross join will generate the square of the number of inputs you give it. If you feed it 20 words, it will generate 400 combinations. If you feed it 1000 words, it will generate 1000000.
This procedure will work. You may have to create a function with it, but it has the right ideas.
Comments
This should do the trick for how to
DECLARE @length Integer
DECLARE @return VarChar(1000)
DECLARE @index Integer
DECLARE @value Integer
SET @length = 12
SET @return = '';
IF @length > 1000
SET @length = 1000;
IF @length <= 0
SELECT @return;
SET @index = 0;
WHILE @index < @length
BEGIN
SET @value = (64 * RAND()) + 32;
SET @return = @return + CHAR(@value);
SET @index = @index + 1;
END
SELECT @return;
If you wanted to make this a user defined function, i liked the option 'steve' had in this article. here
