SQL has extensive text-handling capabilities built in. You can
extract parts of a string using the standard functions. The basic
operators, such as
(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.
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 ...