Searching with Strings

(Page 1 of 4 )

In this fifth part of a series of articles that explain how to work with strings and string data, you'll learn how to search for substrings and more. It is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

5.14  Searching for Substrings

Problem

You want to know whether a given string occurs within another string.

Solution

Use LOCATE().

Discussion

The LOCATE() function takes two arguments representing the substring that you’re looking for and the string in which to look for it. The return value is the position at which the substring occurs, or 0 if it’s not present. An optional third argument may be given to indicate the position within the string at which to start looking.

  mysql> SELECT name, LOCATE('in',name), LOCATE('in',name,3) FROM metal;

name	LOCATE('in',name)  LOCATE('in',name,3)
copper				0		0
gold					0		0
iron					0		0
lead					0		0
mercury					0		0
platinum					5		5
silver				0		0
tin				2		0

LOCATE() uses the collation of its arguments to determine whether the search is case-sensitive. See Recipes 5.6 and 5.9 for information about changing the comparison properties of the arguments if you want to change the search behavior.

Next: 5.15 Using FULLTEXT Searches >>
 

More Database Articles Articles
More By O'Reilly Media

blog comments powered by Disqus