If you are a web developer, you may sometimes find the need to store an XHTML color code in a database. This could be useful, for example, to associate a specific product type with a persistent background color in a search results page. In order to preserve formatting and to generate valid XHTML/CSS, color codes need to be validated. There are many ways to do this in code, but if you believe in leaving data constraints to the database management system (DBMS), then you probably want to do this with a column or table constraint or trigger.
Constraints are by far easier to setup than triggers. They also have the advantage of being part of the data model in a very visible way (so you'll always know they are there). There are many ways to validate a hex color code. For instance, one might convert the entire code to integer -- if it converts without problems to a number within a specific range, then it must be a valid hex color code. One could also process each character individually, ensuring that each character converts to an integer. A third way would be to validate each character individually, ensuring that it falls under a specific character set (in this case, digits 0-9 and letters A-F). I have personally found the third method to perform faster when working in Microsoft SQL Server.
This validation method works as follows:
- First, ensure that the string has exactly six characters after removing all white spaces. In this example, HEX_COLOR is the column name.
(LEN(rtrim(ltrim( [HEX_COLOR] )))=6\ - Second, validate that each character is a valid hex character by using a character index as a referrence:
(charindex(substring(upper([HEX_COLOR]),1,1),'0123456789ABCDEF') >= 1 and charindex(substring(upper([HEX_COLOR]),1,1),'0123456789ABCDEF') <= 16)
This is the complete constraint code for Transact-SQL (MS SQL):
(len(rtrim(ltrim([HEX_COLOR]))) = 6 and (charindex(substring(upper([HEX_COLOR]),1,1),'0123456789ABCDEF') >= 1 and charindex(substring(upper([HEX_COLOR]),1,1),'0123456789ABCDEF') <= 16) and (charindex(substring(upper([HEX_COLOR]),2,1),'0123456789ABCDEF') >= 1 and charindex(substring(upper([HEX_COLOR]),2,1),'0123456789ABCDEF') <= 16) and (charindex(substring(upper([HEX_COLOR]),3,1),'0123456789ABCDEF') >= 1 and charindex(substring(upper([HEX_COLOR]),3,1),'0123456789ABCDEF') <= 16) and (charindex(substring(upper([HEX_COLOR]),4,1),'0123456789ABCDEF') >= 1 and charindex(substring(upper([HEX_COLOR]),4,1),'0123456789ABCDEF') <= 16) and (charindex(substring(upper([HEX_COLOR]),5,1),'0123456789ABCDEF') >= 1 and charindex(substring(upper([HEX_COLOR]),5,1),'0123456789ABCDEF') <= 16) and (charindex(substring(upper([HEX_COLOR]),6,1),'0123456789ABCDEF') >= 1 and charindex(substring(upper([HEX_COLOR]),6,1),'0123456789ABCDEF') <= 16))
For Oracle, try using instr2 instead of charindex, and substr2 instead of substring.
Although storing a hex color code may not always be useful, when it is, the value should always be validated before saving it to the database. The validation may take place at the application level (in the code) or at the database level (via triggers, functions, stored procedures or constraints). The code presented here checks that a color code is valid via a constraint, which seems to be a fast and easy validation method.