w3resource logo


MySQL sound like function

MySQL LOCATE() function

rating Average rating 9 out of 10. Total 28 users rated.

<<PreviousNext>>

Description

MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start. If this position is not mentioned, searching starts from the beginning.

Syntax

LOCATE(substr,str)

LOCATE (search str, str, [position])

Arguments

Name Description
search str A string which will be searched for.
str A string which is going to be searched.
position Position from where (within the second argument) the searching will start .

MySQL Version : 5.6

Pictorial Presentation

MySQL LOCATE function

Example : MySQL LOCATE() function

The following MySQL statement returns the 1st occurrence ‘st’ within the string ‘myteststring’. Since the "st" subsrting is found at fifth position, the function returns 5.

SELECT LOCATE('st','myteststring'); 

Output

MySQL LOCATE()

Example : MySQL LOCATE() function with starting position

The following statement returns the 1st occurrence ‘st’ within the string ‘myteststring’ and the searching will start from the 6th position of the string. The function returns 7, since the substring st is found at seventh position.

SELECT LOCATE('st','myteststring',6);

Output

MySQL LOCATE() EXAMPLE

Example : MySQL LOCATE() function using table

The following MySQL statement returns those rows from the publisher table where the search string ‘at’ exists at least once within the column pub_name.

SELECT pub_name,LOCATE('at',pub_name) 
FROM publisher 
WHERE locate('at',pub_name)>0; 

Sample table : publisher

Output

MySQL LOCATE() EXAMPLE1

View the example

Example : MySQL LOCATE() function with WHERE clause

The following MySQL statement returns those rows from the publisher table where the search string ‘at’ present at least once within the column pub_name. In this statement the 1st locate starts the searching from the beginning of the string and the second searching starts from the 16 position of the string.

SELECT pub_name,LOCATE('at',pub_name),
LOCATE('at',pub_name,16)
FROM publisher 
WHERE LOCATE('at',pub_name)>0;              

Sample table : publisher

Output

MySQL LOCATE() EXAMPLE2

PHP script

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>example1-locate-function - php mysql examples | w3resource</title>
</head>
<body>
<?php
echo "<h2>list of publishers, whose name contains the string 'at' at least once where string is positioned as sixteenth character from the starting : </h2>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='250' align='center'>Publishers name</td><td width='250' align='center'>Output</td><td width='250' align='center'>Output2</td>";
echo "</tr>";
include("../dbopen.php");
$result = mysql_query("SELECT pub_name,LOCATE('at',pub_name) as output, // output is an alias 
LOCATE('at',pub_name,16) as output2 //output2 is an alias 
FROM publisher
WHERE  LOCATE('at',pub_name)>0");
while($row=mysql_fetch_array($result))
{
echo "<tr>";
echo "<td align='center' width='200'>" . $row['pub_name'] . "</td>";
echo "<td align='center' width='200'>" . $row['output'] . "</td>"
echo "<td align='center' width='200'>" . $row['output2'] . "</td>";
echo "</tr>";
}
echo "</table>"
?>
</body>
</html>

All String Functions

MySQL String Functions, slide presentation



We have compiled a number of MySQL Questions and Answers. Check and assess you MySQL skill by visiting those questions and answers.

<<PreviousNext>>