SQL Hacks

Chapter 3. Text Handling

SQL has extensive text-handling capabilities built in. You can extract parts of a string using the standard functions. The basic operators, such as LIKE and || or CONCAT (concatenation), are all that you need for everyday queries. But there are some more exotic facilities, such as full-text indexing and string hashing, that can make your code faster and smarter.

Search for Keywords Without LIKE

You can do a simple keyword search using the LIKE operator. Unfortunately, this can be slow. Fortunately, an efficient keyword search is available in many systems.

Often you must store large chunks of text in a table. For example, suppose you have a table called story, which contains the author of a story and the story itself:

CREATE TABLE story (
  author varchar(100),
  body   varchar(1000)
);
INSERT INTO story (author,body) VALUES('Atzeni'
   ,'Many database systems, through the use of SQL,↵
    are wonderful at collating...');
INSERT INTO story (author,body) VALUES('Adams'
   ,'The definitions involved in understanding SQL databases are big.↵
    You may have thought the distance from your chair to the fridge↵
    was big, but that''s peanuts compared to standard definitions.');
INSERT INTO story (author,body) VALUES('Russell and Cumming'↵
   ,'Often you must store large chunks of text in a table.');

If you wanted to find out which body has the phrase “database system” in it, you could do the following:

SELECT author FROM story
 WHERE body LIKE '%database system%'

This accurately returns matches ...