How to produce random hex color in sql?

Question

It's pretty hard topic for me because SQL is not my best skill ;)

I must insert random hex colors into database row. How can I do it? Is it possible to create function that will draw numbers?

Answer 1

SET [Color] =  '#' +  CONVERT(VARCHAR(max), CRYPT_GEN_RANDOM(3), 2) 

Answer 2

Here is the logic explained below wrapped in a function for MySQL. It's very easy to use.

mysql> select random_color(); +----------------+ | random_color() | +----------------+ | #8F50B4        | +----------------+ 1 row in set (0.00 sec) 

It can be called over and over again and each time it will have a different color.

CREATE FUNCTION `random_color`() RETURNS char(7) CHARSET latin1 DETERMINISTIC BEGIN  DECLARE str CHAR(7);  SET str = concat('#',SUBSTRING((lpad(hex(round(rand() * 10000000)),6,0)),-6));  RETURN str; END; 

This will give you six digit hex number codes in MySQL

SELECT concat('#',SUBSTRING((lpad(hex(round(rand() * 10000000)),6,0)),-6)) 

Here's a great one that will give you incremental colors

SELECT *,         concat('#',SUBSTRING((lpad(hex(@curRow := @curRow + 10),6,0)),-6)) AS color  FROM table         INNER JOIN (SELECT @curRow := 5426175) color_start_point