Convert RGB to Hex with PLSQL

//Convert RGB to Hex with PLSQL

Convert RGB to Hex with PLSQL

Fun with Colors

The fun with colors carries us back to Crayola’s and refrigerator art. One thing I liked about my ole Crayola crayons is that they each came with their name stamped on the side: brick red, mulberry, and pine green. With a very simple table structure, we Convert RGB to Hex with PLSQL. With small effort, you can display these colors in your applications. So why not have a color table? Then you too can flow color name to hex or RGB colors on demand within an Oracle APEX application.

Color Table

You don’t need much. I opted to store the RGB values and a name. The resultant table has five fields:

  • Color primary key
  • Color Name
  • Red Value (0-255)
  • Green Value (0-255)
  • Blue Value (0-255)
  "COLOR_PK"    NUMBER(11,0), 
  "COLOR_RED"   NUMBER(3,0), 

Convert RGB to Hex with PLSQL

Converting red, green, blue color scheme to hex using Oracle PL/SQL is a formula. The first step is to explore how the to_char() function converts a decimal number to a hexadecimal number.

The formula is: to_char(15,’XXX’). Give it a go in your favorite PL/SQL interface.

select to_char(15,'XXX') red from dual;

Result? F

To go the other way around: hex to decimal?

select to_number('F','XX') red from dual;

Display colors through HTML is often easier with six-character long representation of the color value.

Firebrick is RGB(178, 34, 34) or #B22222. Hex B2 is 178. And hex value for decimal 22 is 34.

Hexadecimal colors are really concatenations of 3 hex values: 2 for red, 2 for green, and 2 for blue. The firebrick in hex is B2 – 34 -34. Keeping track of your digits is important. Darkgreen which is RGB(0,100,0) must be represented as #006400 in hex. The digits are significant. So you’ll need to trim spaces and add your padding.

select lpad(trim(to_char(15,'XXX')),2,'0') red from dual;

Read more about lpad at Oracle’s site. Here we are stating that the result must be 2 characters long and be padded with zeros.

    '#' ||
  	lPad(trim(to_char(:red,'XXX')),2,'0')  ||
  	lPad(trim(to_char(:green,'XXX')),2,'0')  ||
  	lPad(trim(to_char(:blue,'XXX')),2,'0')  HEX_COLOR
  from dual;

For my values, I used 178, 34, 34 (firebrick red). My query yielded: ‘#B22222’.

Back to APEX

Colors in APEX

The image shown is a snippet of a color table viewed as a classic report in Oracle Application Express. As shown above, the hex value for the color is result of a formula within the query (calculated column). The sample column is a blending of two tricks. I primed my query with a simple Font Awesome shape to display my color swatch. And then I used two column in my HTML formatting for the classic report column.

HTML Format Report Column

Just to finish it off, here is the query.

	color_name "Color",
	color_red "Red",
	color_green "Green",
	color_blue "Blue",
	'#' ||
  	lPad(trim(to_char(color_red,'XXX')),2,'0')  ||
  	lPad(trim(to_char(color_green,'XXX')),2,'0')  ||
  	lPad(trim(to_char(color_blue,'XXX')),2,'0')  HEX_COLOR,
	'fa-square' square
from tg_colors


Converting RGB to Hex with PLSQL may not be as much fun as a box of Crayola crayons and big pieces of paper. But it is better than stepping outside in a blustery and snowy May 15th!


By |2018-10-10T08:54:53+00:00May 16th, 2016|Oracle PL/SQL|Comments Off on Convert RGB to Hex with PLSQL

About the Author: