Convert RGB color to HEX Color using SQL

Question

Convert RGB color to HEX Color using SQL

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

thomashohner (3/14/2015)
Thanks again Jeff , "Jefe el Sql ",
I ran both convert codes and on my limited test data (1,000 rows) they were both uber fast. Not even a 1 ms.
The real test of course would be production with 500,000 + rows so I should see that on Monday.
However, even though this is the first time I have ever come across this situation, when I googled it, it was clear I was not the first to come across this situation.
This would be a great article for us SSRS/BI folks. I however don't have the skill nor the intellect to do so. I now however am researching more information on Convert because of this.
I can honestly say SQL Server Central beat my Google Fu again.
Thanks again Gentlemen truly a inspiration to us sql noobies:-)

Thanks a lot for the feedback, Thomas.

On the idea of an article, that's a great idea. I didn't know there was even a need for such a thing prior to today. With that in mind, it would be good to title it something that people frequently look for. What searches did you use when you were doing the Yabingooglehoo thing?

I've got a couple of extra minutes to "play" and I'm setting up a test. I'll post that code and the results soon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 2

I searched things like

Convert RGB to Hex color TSQL

RGB column stored as INT how to convert tsql

RGB to hex code SQL

Hex to RGB sql

Convert int rgb to hex SQL

Stuff like that.

Let me tell you the wow factor one of my Colleagues just gave me because the rows on my report match the users application has me smiling ear to ear.

( yes I like SQl so much I work on my days off) shhhh don't tell my wife

Great SQl day today!

***SQL born on date Spring 2013:-)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Awesome. I love helping but when someone that we've helped get's a great wow-factor on top of it all, that makes my day, as well. Thanks again for the feedback on that.

Here's the simple testing I did on all this. Note that I only measured Duration because SET STATISTICS wreaks havoc on Scalar Functions like the original function.

First, here's the simple test data. 5 Million rows. Takes less than 2 seconds on my humble laptop so don't let the big number scare anyone.

-- DROP TABLE #PerfTest;

SELECT TOP 5000000

N = ISNULL(ROW_NUMBER()OVER(ORDER BY (SELECT NULL)),0)

INTO #PerfTest

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

Here's the test harness. I short-circuits all output to a variable to remove variances in disk and display.

DECLARE @Bitbucket CHAR(7)

,@StartTime DATETIME

;

PRINT '========== Baseline Read (No Conversions) ==========';

SELECT @StartTime = GETDATE();

SELECT @Bitbucket = N

FROM #PerfTest;

PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114)

;

PRINT '========== Eirikur''s Direct Code ==========';

SELECT @StartTime = GETDATE();

SELECT @Bitbucket = CHAR(35) + CONVERT(VARCHAR(6),SUBSTRING(CONVERT(BINARY(3),N,1),3,1) + SUBSTRING(CONVERT(BINARY(3),N,1),2,1) + SUBSTRING(CONVERT(BINARY(3),N,1),1,1),2)

FROM #PerfTest;

PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114)

;

PRINT '========== Jeff''s Direct Code ==========';

SELECT @StartTime = GETDATE();

SELECT @Bitbucket = '#'+CONVERT(CHAR(7),CONVERT(BINARY(3),REVERSE(CONVERT(BINARY(3),N))),2)

FROM #PerfTest;

PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114)

;

PRINT '========== Jeff''s Multi-purpose iTVF ==========';

SELECT @StartTime = GETDATE();

SELECT @Bitbucket = ca.RGBCode

FROM #PerfTest st

CROSS APPLY dbo.RGBCode(st.N) ca;

PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114)

;

PRINT '========== Original Scalar Function ==========';

SELECT @StartTime = GETDATE();

SELECT @Bitbucket = [dbo].[ARGB2RGB](N)

FROM #PerfTest;

PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114)

;

Here's the run output. Long live direct calculations! My multi-purpose function came out a bit slow because it uses PARSENAME even for the INTEGER input but still blows the every-lovin' doors off the Scalar Function. A function (iTVF, to be sure) to encapsulate either of our direct code would probably run as fast as the direct code.

========== Baseline Read (No Conversions) ==========

Duration: 00:00:01:123

========== Eirikur's Direct Code ==========

Duration: 00:00:03:357

========== Jeff's Direct Code ==========

Duration: 00:00:02:930

========== Jeff's Multi-purpose iTVF ==========

Duration: 00:00:10:983

========== Original Scalar Function ==========

Duration: 00:09:19:250

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Just for grins, here are the results from the same test harness with only 500,000 rows. The times here will likely be shorter than for your production run because the test table only has one column in it and the output isn't going anywhere but to the bit bucket.

(500000 row(s) affected)

========== Baseline Read (No Conversions) ==========

Duration: 00:00:00:107

========== Eirikur's Direct Code ==========

Duration: 00:00:00:337

========== Jeff's Direct Code ==========

Duration: 00:00:00:297

========== Jeff's Multi-purpose iTVF ==========

Duration: 00:00:01:067

========== Original Scalar Function ==========

Duration: 00:00:55:427

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

Brilliant as always Jeff!

Having had the first portion of my industrial strength espresso this morning it dawned on me that simple bit-bashing would do the job

😎

Reversing the byte order

DECLARE @COLOURINT INT = 14745599;

-- 14745599 in HEX is 0xE0FFFF and we want to reverse the

-- byte order which produces HEX 0xFFFFE0, decimal 16777184

SELECT

-- mask out the left most byte (& 255) and

-- shift it 2 positions to the right

((@COLOURINT & 255) * 65536)

-- mask out the middle bit but no need to

-- shift it

+ (@COLOURINT & 65280)

-- shift the right most bit to the left most

-- position

+ ((@COLOURINT) / 65536);

The addition to Jeff's test harness

PRINT '========== Eirikur''s Bit Bashing ==========';

SELECT @StartTime = GETDATE();

SELECT @Bitbucket = '#'+CONVERT(CHAR(7),CONVERT(BINARY(3),(((N & 255) * 65536) + (N & 65280) + ((N) / 65536))),2)

FROM #PerfTest;

PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114)

;

Output from Jeff's test harness

========== Baseline Read (No Conversions) ==========

Duration: 00:00:01:033

========== Eirikur's Direct Code ==========

Duration: 00:00:02:787

========== Eirikur's Bit Bashing ==========

Duration: 00:00:02:067

========== Jeff's Direct Code ==========

Duration: 00:00:02:133

Edit: Comments and test code.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 6

Quick note on the sys.fn_varbintohexstr function, DON'T use it, use CONVERT instead. A little while back I was working with a large set of MD5 checksums and the original code used the sys.fn_varbintohexstr function, changed it to CONVERT and shaved off around 80%.

😎

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

Further on the RGB to HEX conversion, it is simply working with 3 out of four octets in an integer, swapping octets 1 and 3, A12-B34-C56 becomes C56 B34-A12. Reversing an integer number simply casts the number to a character string and reverses the string producing results more like 65C-43B-21A.

😎

If we look at the value from the sample earlier, Decimal 14745599 / HEX 0xE0FFFF, swapping the octets should produce Decimal 16777184 / HEX 0XFFFFE0, REVERSE(14745599) on the other hand produces Decimal 3750197 / HEX 0x393935.

Looking at the individual bits it is obvious that the third line (reverse) is not bringing back the correct value.

For completeness, here is the IntToBits function

CREATE FUNCTION dbo.iTVF_INT_TO_BITS

(

@INTVAL INT

)

RETURNS TABLE

AS

RETURN

(

SELECT

(1 - SIGN(ABS(1+SIGN(@INTVAL)))) * ABS(SIGN(@INTVAL)) AS BIT_01

,1 - SIGN(ABS((@INTVAL & 1073741824 ) - 1073741824 )) AS BIT_02

,1 - SIGN(ABS((@INTVAL & 536870912 ) - 536870912 )) AS BIT_03

,1 - SIGN(ABS((@INTVAL & 268435456 ) - 268435456 )) AS BIT_04

,1 - SIGN(ABS((@INTVAL & 134217728 ) - 134217728 )) AS BIT_05

,1 - SIGN(ABS((@INTVAL & 67108864 ) - 67108864 )) AS BIT_06

,1 - SIGN(ABS((@INTVAL & 33554432 ) - 33554432 )) AS BIT_07

,1 - SIGN(ABS((@INTVAL & 16777216 ) - 16777216 )) AS BIT_08

,1 - SIGN(ABS((@INTVAL & 8388608 ) - 8388608 )) AS BIT_09

,1 - SIGN(ABS((@INTVAL & 4194304 ) - 4194304 )) AS BIT_10

,1 - SIGN(ABS((@INTVAL & 2097152 ) - 2097152 )) AS BIT_11

,1 - SIGN(ABS((@INTVAL & 1048576 ) - 1048576 )) AS BIT_12

,1 - SIGN(ABS((@INTVAL & 524288 ) - 524288 )) AS BIT_13

,1 - SIGN(ABS((@INTVAL & 262144 ) - 262144 )) AS BIT_14

,1 - SIGN(ABS((@INTVAL & 131072 ) - 131072 )) AS BIT_15

,1 - SIGN(ABS((@INTVAL & 65536 ) - 65536 )) AS BIT_16

,1 - SIGN(ABS((@INTVAL & 32768 ) - 32768 )) AS BIT_17

,1 - SIGN(ABS((@INTVAL & 16384 ) - 16384 )) AS BIT_18

,1 - SIGN(ABS((@INTVAL & 8192 ) - 8192 )) AS BIT_19

,1 - SIGN(ABS((@INTVAL & 4096 ) - 4096 )) AS BIT_20

,1 - SIGN(ABS((@INTVAL & 2048 ) - 2048 )) AS BIT_21

,1 - SIGN(ABS((@INTVAL & 1024 ) - 1024 )) AS BIT_22

,1 - SIGN(ABS((@INTVAL & 512 ) - 512 )) AS BIT_23

,1 - SIGN(ABS((@INTVAL & 256 ) - 256 )) AS BIT_24

,1 - SIGN(ABS((@INTVAL & 128 ) - 128 )) AS BIT_25

,1 - SIGN(ABS((@INTVAL & 64 ) - 64 )) AS BIT_26

,1 - SIGN(ABS((@INTVAL & 32 ) - 32 )) AS BIT_27

,1 - SIGN(ABS((@INTVAL & 16 ) - 16 )) AS BIT_28

,1 - SIGN(ABS((@INTVAL & 8 ) - 8 )) AS BIT_29

,1 - SIGN(ABS((@INTVAL & 4 ) - 4 )) AS BIT_30

,1 - SIGN(ABS((@INTVAL & 2 ) - 2 )) AS BIT_31

,1 - SIGN(ABS((@INTVAL & 1 ) - 1 )) AS BIT_32

)

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 8

That Bit Bash is sweet! Wow, so cool. I'm not sure why this is exciting but it is. 😀

***SQL born on date Spring 2013:-)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Eirikur Eiriksson (3/15/2015)
Brilliant as always Jeff!
Having had the first portion of my industrial strength espresso this morning it dawned on me that simple bit-bashing would do the job
😎
Reversing the byte order
DECLARE @COLOURINT INT = 14745599;
-- 14745599 in HEX is 0xE0FFFF and we want to reverse the
-- byte order which produces HEX 0xFFFFE0, decimal 16777184
SELECT
-- mask out the left most byte (& 255) and
-- shift it 2 positions to the right
((@COLOURINT & 255) * 65536)
-- mask out the middle bit but no need to
-- shift it
+ (@COLOURINT & 65280)
-- shift the right most bit to the left most
-- position
+ ((@COLOURINT) / 65536);
The addition to Jeff's test harness
PRINT '========== Eirikur''s Bit Bashing ==========';
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = '#'+CONVERT(CHAR(7),CONVERT(BINARY(3),(((N & 255) * 65536) + (N & 65280) + ((N) / 65536))),2)
FROM #PerfTest;
PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114)
;
Output from Jeff's test harness
========== Baseline Read (No Conversions) ==========
Duration: 00:00:01:033
========== Eirikur's Direct Code ==========
Duration: 00:00:02:787
========== Eirikur's Bit Bashing ==========
Duration: 00:00:02:067
========== Jeff's Direct Code ==========
Duration: 00:00:02:133
Edit: Comments and test code.

Speaking of "brilliant", nicely done, Eirikur! Heh... you really put the hex on that problem. There's only 1 in 10000 people that would know something like that. The other 65,535 would have no clue! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Eirikur Eiriksson (3/15/2015)
Quick note on the sys.fn_varbintohexstr function, DON'T use it, use CONVERT instead. A little while back I was working with a large set of MD5 checksums and the original code used the sys.fn_varbintohexstr function, changed it to CONVERT and shaved off around 80%.
😎

+1000. That's what I told Thomas earlier. The sys.fn_varbintohexstr function calls yet another scalar function called sys.fn_varbintohexsubstring and it has a nasty ol' WHILE loop in it to do the conversion. Really, really slow as our testing has shown compared to the direct methods or even using one of those direct methods in an iTVF.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

thomashohner (3/15/2015)
That Bit Bash is sweet! Wow, so cool. I'm not sure why this is exciting but it is. 😀

Heh... I can't speak for anyone else but I like this kind of stuff because it supports one of the mantras in my signature line...

"Just because you can do something in T-SQL doesn't mean you SHOULDN'T!" 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 12

Jeff Moden (3/15/2015)
Speaking of "brilliant", nicely done, Eirikur! Heh... you really put the hex on that problem. There's only 1 in 10000 people that would know something like that. The other 65,535 would have no clue! 😛

Thank you for the comment Jeff, slightly flattered:-D This is yet another example of the SSC at it's best, now I'm just waiting for you or the others to half the execution time:w00t:.

Digressing slightly, some years back before the XML era, packed numerics (bitmasks etc.) or RLE (ASN.1) data was quit common, for me it was like learning to ride a bike, something one doesn't forget.

😎

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 13

thomashohner (3/15/2015)
That Bit Bash is sweet! Wow, so cool. I'm not sure why this is exciting but it is. 😀

It is short, concise and straight to the point, nothing complex if one can get ones thoughts out of the decimal space. Recommend you look further into the subject and of course if you have any questions, feel free to ask.

😎

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

While all this Bit Bobbling, and Digit Dicing is fun and groovy :cool:, isn't the problem that you are storing your RGB values as a CSV string?

If they were separate columns in the table, you could just use this formula in SSRS and leave the formatting to the front end 😉

= String.Format("#{0:x2}{1:x2}{2:x2}",Fields!R.Value,Fields!G.Value,Fields!B.Value)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 15

mister.magoo (3/15/2015)
While all this Bit Bobbling, and Digit Dicing is fun and groovy :cool:, isn't the problem that you are storing your RGB values as a CSV string?
If they were separate columns in the table, you could just use this formula in SSRS and leave the formatting to the front end 😉
= String.Format("#{0:x2}{1:x2}{2:x2}",Fields!R.Value,Fields!G.Value,Fields!B.Value)

Hi Magoo,

That was bad information on my part. The actual column in the database is stored as a int. The RGB was a computed column.

***SQL born on date Spring 2013:-)