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)






SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net