LOCATE()
The LOCATE() function takes three arguments. The first argument is the string expression you are trying to
find. The second argument is the string that may contain the string for which you are looking. The final
argument is the position from which you want to start looking. If you wanted to find the word "brown" in the
string "The quick brown fox jumped over the lazy dog", you would use the following statement:
SELECT LOCATE("brown", "The quick brown fox jumped over the lazy dog", 1)
This statement would return 10. This is the first position it found the string for which you were looking.
You can combine functions to get more out of them. For example, you could combine the
SUBSTRING() function with the LOCATE() function to return a string that may exist in a BLOB data
type. It would look something like the following:
SELECT SUBSTRING(Column1, LOCATE("Doctor", Column1, 1))
The output from this function would be everything after the word Doctor in that column. For example,
say that Column1 had the value "Dr. Ramirez" and you only wanted his last name. Using the
previous syntax, the statement would look like the following:
SELECT SUBSTRING("Dr. Ramirez", (LOCATE(".","Dr. Ramirez",1) + 2))
The LOCATE function would evaluate to 3. You know that there is a space between the name and the
title. The function also starts counting at the position given as the second argument. To get just the last
name, you have to add two to the result of the LOCATE function. That would be the start of the position
for the last name.Combining functions can be a powerful tool. It can also save time and logic in your applications.REPLACE()
The REPLACE()function replaces a string expression with another string expression. This function takes
three arguments. The first argument is the main string expression. The second argument is the string you
want replaced, and the last argument is the string that is going to replace what is in argument two. For
example, if you have a string "FileName.xxx", and you want to replace the "xxx" with "123" Your
function call would look like the following:
SELECT REPLACE("FileName.xxx","xxx","123")Your result would be
"FileName.123"REVERSE()
The REVERSE() function simply reverses the string expression that is given as its argument. For example,
suppose you needed to reverse the string "Hello". Your function call would be
SELECT REPLACE("Hello")The results of this function call would be
"olleH"UCASE(), LCASE()
The UCASE() and the LCASE() function takes whatever string expression that it has as its argument and
either makes it all upper- or all lowercase. For example
SELECT UCAASE("mark")produces
"MARK"and
SELECT LCASE("MARK")returns
"mark"As you can see, MySQL has a number of useful and not so useful string manipulation functions. These
can save time on the client, or allow you to create robust reports without leaving the database server.