Populate column with 3 random strings from a list

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

General SQL Server Forums

Database Design and Application Architecture

Populate column with 3 random strings from a list

Author

Topic

bernard75
Starting Member

3 Posts

Posted - 2012-07-12 : 12:01:11

Long story short, i would like to populate a column with 3 random strings from a list. I have this:
UPDATE table SET column = ELT(FLOOR(RAND()*3)+1, 'string1', 'string2', 'string3');

Just need something to add 2 more strings. So the output will be:
string1 string2 string3
string3 string1 string2
string2 string3 string1
...

I hope thats no utter gibberish and would appreciate any help.
Cheers
Bernard

www.worldcupfancamp.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 12:25:41

ELT isn't a SQL server function. Is that MySql? If you are using SQL Server 2012 you can make use of the CHOOSE function. But, I'm not 100% what your actual question is. Are you asking how to populate three columns from a set of three distinct values such that each column (in that row) gets a unique value?

bernard75
Starting Member

3 Posts

Posted - 2012-07-12 : 12:36:02

Ooops, i thought it is:
http://www.tutorialspoint.com/sql/sql-string-functions.htm

What i am trying to accomplish, is to populate 1 column with 3 random values:
string1 string2 string3
string3 string1 string2
string2 string3 string1

www.worldcupfancamp.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 14:00:34

For some reason I'm getting NULL values with the CHOOSE function, but here is one way

CREATE TABLE Foo (Col1 VARCHAR(20), Col2 VARCHAR(20), Col3 VARCHAR(20))
GO

INSERT Foo 
VALUES(
CHOOSE((ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 3) + 1, 'string1', 'string2', 'string3'),
CHOOSE((ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 3) + 1, 'string1', 'string2', 'string3'),
CHOOSE((ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 3) + 1, 'string1', 'string2', 'string3')
)
GO 10

SELECT *
FROM Foo

DROP TABLE Foo

Also, you could select from a table:

CREATE TABLE Val (Col VARCHAR(20));
INSERT Val VALUES ('string1'), ('string2'), ('string3');

CREATE TABLE Foo (Col1 VARCHAR(20), Col2 VARCHAR(20), Col3 VARCHAR(20));
GO

INSERT Foo 
VALUES(
	(SELECT TOP 1 Col FROM Val ORDER BY NEWID()),
	(SELECT TOP 1 Col FROM Val ORDER BY NEWID()),
	(SELECT TOP 1 Col FROM Val ORDER BY NEWID())
)
GO 10

SELECT *
FROM Foo

DROP TABLE Foo
DROP TABLE Val

bernard75
Starting Member

3 Posts

Posted - 2012-07-13 : 03:04:24

Not what i was looking for, but it helps, thx.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources