• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Finding the location of a string in a string with MySQL

Finding the location of a string in a string with MySQL

There may be times when you need to find the location of a string within a string with MySQL. The LOCATE() function allows you to do this and I show some examples in this post of how to do it. The next MySQL post on this blog (this time next week) will combine LOCATE() with SUBSTRING() to extract substrings from a string.

Using Locate

LOCATE works like this:

LOCATE(<string to find>, <string to find it in>)

or

LOCATE(<string to find>, <string to find it in>, <position to start at>)

For example, to find the location of "oranges" in the string "apples oranges pears bananas apples oranges" do this:

SELECT LOCATE("oranges", "apples oranges pears bananas apples oranges");

This will return 8. To find the next occurance of "oranges" in the source string, pass the third parameter to the function telling it to start from position 9 in this example:

SELECT LOCATE("oranges", "apples oranges pears bananas apples oranges", 9);

This will now return 37. Note that in these examples if 8 had been passed as the starting position then 8 would have been returned.

Combining with SUBSTRING

In next week’s MySQL post I’ll look at how to combine the LOCATE function with SUBSTRING to extract a substring based on the position of another string. The examples used extract data from an XML string stored in the database.

Check Out These Related posts:

  1. How to use letters and roman numerals instead of numbers in an HTML ordered list
  2. Selecting substrings with MySQL using LOCATE and SUBSTRING
  3. HTML/CSS background-position
  4. Untar tar.gz

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap