∞

Convert RGB to HEX in SQL Server

March 16, 2011 at 16:27 (Blogroll)

CREATE function [dbo].[ConvertToBase]
(
@val as BigInt,
@base as int
)
returns varchar(63)
as
Begin
/* Check if we get the valid base */
If (@val<0) OR (@base 36) Return Null;
IF (@val = 0)  Return ’00′;
IF (@val = 1)  Return ’01′;
IF (@val = 2)  Return ’02′;
IF (@val = 3)  Return ’03′;
IF (@val = 4)  Return ’04′;
IF (@val = 5)  Return ’05′;
IF (@val = 6)  Return ’06′;
IF (@val = 7)  Return ’07′;
IF (@val = 8)  Return ’08′;
IF (@val = 9)  Return ’09′;
IF (@val = 10) Return ’0A’;
IF (@val = 11) Return ’0B’;
IF (@val = 12) Return ’0C’;
IF (@val = 13) Return ’0D’;
IF (@val = 14) Return ’0E’;
IF (@val = 15) Return ’0F’;

/* variable to hold final answer */
Declare @answer as varchar(63);

/* Following variable contains all
possible alpha numeric letters for any base
*/
Declare @alldigits as varchar(36);
Set @alldigits=’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’

/* Set the initial value of
final answer as empty string
*/
Set @answer=”;

/* Loop until your source value is greater than 0 */
While @val>0
Begin
Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
Set @val = @val / @base;
End

/* Return the final answer */
return @answer;
End

——————————————————————-

CREATE FUNCTION [dbo].[GetHEXfromRGB]
(
@input as varchar(255)
)
RETURNS varchar(63)
AS
BEGIN
Declare @r int;
set @r = CAST(LEFT(@input, CHARINDEX(‘,’,@input)-1) AS INT);

Declare @temp as varchar(255);
Set @temp = CAST(RIGHT(@input, LEN(@input) – CHARINDEX(‘,’,@input)) AS varchar(255))

Declare @g as int;
set @g = CAST(LEFT(@temp, CHARINDEX(‘,’,@temp)-1) AS INT)

Declare @b as int;
Set @b = CAST(RIGHT(@temp, LEN(@temp) – CHARINDEX(‘,’,@temp)) AS INT)

return dbo.converttobase(@r, 16) + dbo.converttobase(@g, 16) + dbo.converttobase(@b, 16)
END

——————————————————————-

Usage: select dbo.GetHEXfromRGB(’125,161,134′)
Resultant HEX Value: 7DA186