Hack 16. Search for a String Across Columns

A string that you are looking for might be in any one of several columns. You can search them all at once rather than individually.

Say you have a table of people's bedroom colors, as shown in Table 3-1. Does anyone have yellow anywhere in their room?

Table 3-1. The bedroom table

name	floorcolor	ceilingcolor	wallcolor
Jim	RED	GREEN	YELLOW
Bob	YELLOW	BLUE	BLACK
Allan	BLUE	PINK	BLACK
George	BLUE	GREEN	OAK

To determine which people have yellow in their rooms, you could say:

SELECT name FROM bedroom
WHERE floorcolor = 'YELLOW'
OR ceilingcolor = 'YELLOW'
OR wallcolor = 'YELLOW'

However, in that case the search string is repeated for each column. Using OR increases the chances of creating careless errors in your queries. Instead, you could use CONCAT to do this in one line:

SELECT name FROM bedroom
WHERE CONCAT(floorcolor,ceilingcolor,wallcolor) like '%YELLOW%'

A downside of this style of query is that the database system will not necessarily run the query efficiently. It is hard for the system to use indexes when using concatenated strings, and using a wildcard in a LIKE expression will not usually employ an index (if the wildcard is not near the start of the pattern, an index might be used). However, the performance hit will be noticeable only for large data sets.

With this CONCAT approach, the colors related to George would become BLUEGREENOAK. If there actually was a color called GREENOAK, you could not be sure whether GREEN and OAK are in different columns or GREENOAK is in one column. To help avoid confusion, you can add a separator:

SELECT name FROM bedroom
WHERE CONCAT(':',floorcolor,':',ceilingcolor,':',wallcolor,':') 
 like '%:YELLOW:%'

If a color can be null, it must be wrapped in COALESCE or NVL; for example, COALESCE(floorcolor,'').

COALESCE is the SQL92 standard way of doing this. In Oracle, you can also use NVL. SQL Server and MySQL also allow IFNULL.

You also can use this trick to see whether a floorcolor has one of a range of values, mimicking the IN operator.

Using the IN operator you can say:

SELECT name FROM bedroom
WHERE floorcolor IN ('BLUE','BLACK','GREEN')

You can rewrite this as:

SELECT name FROM bedroom
WHERE 'BLUE BLACK GREEN' LIKE CONCAT('%',floorcolor,'%')

You can combine these techniques to query whether anyone has a ceilingcolor that is used in someone else's bedroom. This needs a self-join:

SELECT l.name,r.name
FROM bedroom AS l JOIN bedroom AS r
 ON (CONCAT(r.floorcolor,r.ceilingcolor,r.wallcolor)
 LIKE CONCAT('%',l.ceilingcolor,'%')
 AND l.name != r.name)