Convert RGB to HEX in SQL Server

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

Advertisements

4 thoughts on “Convert RGB to HEX in SQL Server

  1. Think there is a typo in this line

    If (@val<0) OR (@base 36) Return Null;

    Shouldn't that be

    If (@val 36) Return Null;

    otherwise SQL throws and error with this line

    NIce work, helped me out

    JIm

  2. ok the blog is turncating the line


    If (@val<0) OR (@base 36) Return Null;

    should have GT sign between @base and 36

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s