March 14, 2015 at 11:56 am
Hello all I have a field in one of my tables that has the RGB colors stored as 255,255,255 format. Is there a way to convert this to Hex color code to be used inside SSRS for a conditional color expression?
Thanks!
Thomas
***SQL born on date Spring 2013:-)
March 14, 2015 at 12:08 pm
thomashohner (3/14/2015)
Hello all I have a field in one of my tables that has the RGB colors stored as 255,255,255 format. Is there a way to convert this to Hex color code to be used inside SSRS for a conditional color expression?Thanks!
Thomas
I'm not familiar with the "Hex Color Code" inside of SSRS. What datatype should it be and what does one look like? Should they be character based and look like the following? #FFFFFF
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2015 at 12:11 pm
Yes sir,
Did I say it wrong on what data type it should be?
***SQL born on date Spring 2013:-)
March 14, 2015 at 12:15 pm
I also have the color stored as a Int
So I have either this format 255,255,224 or 14745599
***SQL born on date Spring 2013:-)
March 14, 2015 at 12:44 pm
thomashohner (3/14/2015)
Yes sir,Did I say it wrong on what data type it should be?
Oh no... You did fine. It's just that I can't even spell "SSRS" and, although my assistant DBA's name is "Google", I wanted to be absolutely sure. 😛
Here's a quick example of the code you could use for an Inline Table Valued Function to solve the string version. I'm playing with the INT solution and intend to write a function that will take either the string version or the INT version.
DECLARE @StringRGB VARCHAR(11);
SELECT @StringRGB = '127,16,0';
WITH
cteParms AS
(
SELECT HexDigits = '0123456789ABCDEF'
,R = MAX(CASE WHEN split.ItemNumber = 1 THEN Item ELSE 0 END)
,G = MAX(CASE WHEN split.ItemNumber = 2 THEN Item ELSE 0 END)
,B = MAX(CASE WHEN split.ItemNumber = 3 THEN Item ELSE 0 END)
FROM dbo.DelimitedSplit8K(@StringRGB,',') split
)
SELECT RGBHexColor = '#'
+ SUBSTRING(HexDigits,R/16+1,1)+SUBSTRING(HexDigits,R%16+1,1)
+ SUBSTRING(HexDigits,G/16+1,1)+SUBSTRING(HexDigits,G%16+1,1)
+ SUBSTRING(HexDigits,B/16+1,1)+SUBSTRING(HexDigits,B%16+1,1)
FROM cteParms
;
If you don't know what "dbo.DelimitedSplit8K" is, you can find it in the "Resources" section of the following article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2015 at 12:49 pm
Wow Jeff you always amaze!
Just blown away 😀
I was trying to figure out a efficient solution. I found this Scalar Function online it use the INT version of my column . However I prefer even though it works not to use it as I know it might cause some performance issues.
As this query has the potential to run into several hundred thousand rows.
CREATE FUNCTION [dbo].[ARGB2RGB]
(@ARGB AS BIGINT )
RETURNS VARCHAR (15)
AS
BEGIN
DECLARE @Octet1 TINYINT
DECLARE @Octet2 TINYINT
DECLARE @Octet3 TINYINT
DECLARE @Octet4 TINYINT
DECLARE @RestOfColor BIGINT
SET @Octet1 = @ARGB / 16777216
SET @RestOfColor = @ARGB - ( @Octet1 * CAST(16777216 AS BIGINT) )
SET @Octet2 = @RestOfColor / 65536
SET @RestOfColor = @RestOfColor - ( @Octet2 * 65536 )
SET @Octet3 = @RestOfColor / 256
SET @Octet4 = @RestOfColor - ( @Octet3 * 256 )
RETURN
'#' + RIGHT(sys.fn_varbintohexstr(@Octet4), 2)
+ RIGHT(sys.fn_varbintohexstr(@Octet3), 2)
+ RIGHT(sys.fn_varbintohexstr(@Octet2), 2)
END
***SQL born on date Spring 2013:-)
March 14, 2015 at 12:58 pm
This will really make it so you don't want to use the scalar function you posted. The sys.fn_varbintohexstr function calls yet another scalar function called sys.fn_varbintohexsubstring and it not only contains flow control logic, but it also contains a WHILE loop.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2015 at 12:59 pm
Hi Thomas,
here is a quick example of RGB triplet string into a hex conversion
😎
DECLARE @STRCD VARCHAR(11) = '255,128,255';
SELECT
CONVERT(VARCHAR(11),CONVERT(BINARY(1),CONVERT(INT,SUBSTRING(@STRCD,1,CHARINDEX(',',@STRCD)-1),0),0)
+ CONVERT(BINARY(1),CONVERT(INT,SUBSTRING(@STRCD,CHARINDEX(',',@STRCD) + 1,CHARINDEX(',',@STRCD)-1),0),0)
+ CONVERT(BINARY(1),CONVERT(INT,RIGHT(@STRCD,CHARINDEX(',',REVERSE(@STRCD),1)-1),0),0),1);
Result
0xFF80FF
March 14, 2015 at 2:00 pm
Huh... It's been a long time since I've played with hex in SQL Server. I believe I may have an even better and faster way than what I posted. It might make the integer problem a whole lot easier, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2015 at 2:06 pm
I am currently building the report with my test data. Its small so my original function is working well but wont on larger data sets. The stuff you pointed out about my original function scares the living tar out of me. You and Erik is why I come here its a amazing learning experience!
Excited to see what you come up with for the INT column. I have been playing and dissecting both yours and Eriks code and I'm just blown away how quick you guys did that. (That's the way I learn to take things apart and see how they move.)Some day I want to be there answering questions instead of asking them!
Thanks so much guys. 😀
***SQL born on date Spring 2013:-)
March 14, 2015 at 2:20 pm
With the integer you will have to reverse the byte order, here is a quick example
😎
DECLARE @COLOURINT INT = 14745599;
SELECT CHAR(35) + CONVERT(VARCHAR(6),SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),3,1) + SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),2,1) + SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),1,1),2)
Result
#FFFFE0
March 14, 2015 at 2:26 pm
Holy smokes Batman
That is awesome Erik!
I can put that directly into the code and this makes it where I don't have to install any other objects beside my SP.
Just put it in the Update Statement sweet!!
I love SQL Server Central !!
So much thanks to you and Jeff. Now After I finish my report to understand what you guys were doing.
***SQL born on date Spring 2013:-)
March 14, 2015 at 4:31 pm
thomashohner (3/14/2015)
I am currently building the report with my test data. Its small so my original function is working well but wont on larger data sets. The stuff you pointed out about my original function scares the living tar out of me. You and Erik is why I come here its a amazing learning experience!Excited to see what you come up with for the INT column. I have been playing and dissecting both yours and Eriks code and I'm just blown away how quick you guys did that. (That's the way I learn to take things apart and see how they move.)Some day I want to be there answering questions instead of asking them!
Thanks so much guys. 😀
Heh... OMG!!! Was this ever fun to revisit! Thank YOU for asking the question. I taught myself something new about CONVERT (the new functionality is available in 2008 and above) on this go around. I also avoided the use of DelimitedSplit8k so that we wouldn't have to worry so much about dependent code within the function. Seemed appropriate in this case since PARSENAME also provided some serious "shortcut" technology in resolving both types of inputs.
This will handle both types of inputs that you asked for and, I believe, in a spritely fashion. As usual, details are in the code where they belong.
CREATE FUNCTION dbo.RGBCode
/**********************************************************************************************************************
Purpose:
Given an "RGB" value in the form of either "R,G,B" where each value is a decimal octet (0-255) or single
"DecimalColorValue" that represents the full color, convert the given value to an HTML Color Value in the form of
"#rrggbb" where "rr", "gg", and "bb" are two character representations of hex bytes.
Programmer's Notes:
1. Note that this function uses functionality from the CONVERT function that is only available in SQL Server 2008
and above. It will NOT work in 2005 and below.
2. If any individual parameter cannot be implicitly converted to an INT, the code will fail with the following
error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'x38' to data type int.
3. No error checking is done for negative numbers or octets being in the range of 0-255 and will return incorrect
results if such problem parameters are passed in.
4. If more than 3 delimited values are provided as the input, only 3 of the values will be used from right to
left as "R,G,B".
Usage Examples:
--===== Simple Single Value Syntax using "R,G,B" values (Strawberry Color #BE2625, in this case)
SELECT RGBCode FROM dbo.RGBCode('190,38,37')
;
--===== This will return the same value as if '190,38,37' were used. See Note 4 in the "Programmer's Notes" above.
SELECT RGBCode FROM dbo.RGBCode('100,190,38,37')
;
--===== Simple Single Value Syntax using a "DecimalColorValue" value (Strawberry Color #BE2625, in this case).
-- Note that the code does a byte reversal to support when the decimal values come from a GUI where the LSB is
furthest to the right and the MSB is furthest to the left at the machine language level.
SELECT RGBCode FROM dbo.RGBCode(2434750)
;
--===== Multi-row Syntax using "R,G,B" values.
SELECT ca.RGBCode
FROM dbo.SomeTable st
CROSS APPLY dbo.RGBCode(st.RGBColumn) ca
;
--===== Multi-row Syntax using a "DecimalColorValue" value.
-- Note that the code does a byte reversal to support when the decimal values come from a GUI where the LSB
and MSB are reversed compared to SQL Server returns at the machine language level.
SELECT ca.RGBCode
FROM dbo.SomeTable st
CROSS APPLY dbo.RGBCode(st.DecimalColorValue) ca
;
Revision History:
Rev 00 - 14 Mar 2015 - Jeff Moden
- Initial creation and partial unit testing.
- Reference: http://www.sqlservercentral.com/Forums/Topic1668566-391-1.aspx (Original requirement)
- Reference: http://www.december.com/html/spec/colorcodes.html (HTML Color Codes)
**********************************************************************************************************************/
--===== Define the I/O for this function
(@StringRGB VARCHAR(20))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
cteReDelim AS ( --=== Prepare for parsing with PARSENAME.
SELECT Newstring = REPLACE(@StringRGB,',','.')
)
,cteRGBHex AS ( --=== Parses the input string whether it is "R,G,B" or "DecimalValue"
SELECT RGB = CONVERT
( --== Convert each part to a decimal value and add them up.
BINARY(3)
,ISNULL(PARSENAME(NewString,1),0) --Only one filled if single decimal passed in.
+ISNULL(PARSENAME(NewString,2),0)*256
+ISNULL(PARSENAME(NewString,3),0)*65536
)
FROM cteReDelim
)
,cteReverse AS ( --=== Reverses the bytes if a single decimal value was passed in.
SELECT RGB = CASE
WHEN @StringRGB LIKE '%,%'
THEN RGB
ELSE CONVERT(BINARY(3),REVERSE(RGB))
END
FROM cteRGBHex
)
--===== Convert the resulting 0xHEX to a string and prefix with "#".
SELECT RGBCode = '#' + CONVERT(CHAR(7),RGB,2)
FROM cteReverse
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2015 at 4:44 pm
Eirikur Eiriksson (3/14/2015)
With the integer you will have to reverse the byte order, here is a quick example😎
DECLARE @COLOURINT INT = 14745599;SELECT CHAR(35) + CONVERT(VARCHAR(6),SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),3,1) + SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),2,1) + SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),1,1),2)
Result
#FFFFE0
I make no claims of performance, in this case, and have no clue if it will be better or worse for performance (out of time for today). I just want to show what is possible with the new functionality available in CONVERT as of 2008 that does the same thing as Eirikur's good code above..
DECLARE @COLOURINT INT = 14745599;
SELECT '#'+CONVERT(CHAR(7),CONVERT(BINARY(3),REVERSE(CONVERT(BINARY(3),@COLOURINT))),2);
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2015 at 5:31 pm
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:-)
***SQL born on date Spring 2013:-)
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply