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?

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,'').

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)