Mysql Query to Search a String in Database
MySQL LOCATE() function
LOCATE() function
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 . |
Syntax Diagram:
MySQL Version: 5.6
Video Presentation
Pictorial Presentation
Example: MySQL LOCATE() function
The following MySQL statement returns the 1st occurrence 'st' within the string 'myteststring'. Since the "st" subsrting is found at the fifth position, the function returns 5.
Code:
SELECT LOCATE('st','myteststring'); Sample Output:
mysql> SELECT LOCATE('st','myteststring'); +-----------------------------+ | LOCATE('st','myteststring') | +-----------------------------+ | 5 | +-----------------------------+ 1 row in set (0.02 sec) 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.
Code:
SELECT LOCATE('st','myteststring',6); Sample Output:
mysql> SELECT LOCATE('st','myteststring',6); +-------------------------------+ | LOCATE('st','myteststring',6) | +-------------------------------+ | 7 | +-------------------------------+ 1 row in set (0.00 sec) 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.
Code:
SELECT pub_name,LOCATE('at',pub_name) FROM publisher WHERE locate('at',pub_name)>0; Sample table: publisher
Sample Output:
mysql> SELECT pub_name,LOCATE('at',pub_name) -> FROM publisher -> WHERE locate('at',pub_name)>0; +--------------------------+-----------------------+ | pub_name | LOCATE('at',pub_name) | +--------------------------+-----------------------+ | Jex Max Publication | 15 | | BPP Publication | 11 | | New Harrold Publication | 19 | | Mountain Publication | 16 | | Summer Night Publication | 20 | +--------------------------+-----------------------+ 5 rows in set (0.03 sec) 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 16th position of the string.
Code:
SELECT pub_name,LOCATE('at',pub_name), LOCATE('at',pub_name,16) FROM publisher WHERE LOCATE('at',pub_name)>0; Sample table: publisher
Sample Output:
mysql> SELECT pub_name,LOCATE('at',pub_name), -> LOCATE('at',pub_name,16) -> FROM publisher -> WHERE LOCATE('at',pub_name)>0; +--------------------------+-----------------------+--------------------------+ | pub_name | LOCATE('at',pub_name) | LOCATE('at',pub_name,16) | +--------------------------+-----------------------+--------------------------+ | Jex Max Publication | 15 | 0 | | BPP Publication | 11 | 0 | | New Harrold Publication | 19 | 19 | | Mountain Publication | 16 | 16 | | Summer Night Publication | 20 | 20 | +--------------------------+-----------------------+--------------------------+ 5 rows in set (0.00 sec) PHP script
<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>example1-locate-function - php mysql examples | w3resource</title> <meta name="description" content="example1-locate-function - php mysql examples | w3resource"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css"> </head> <body> <div class="container"> <div class="row"> <div class="col-md-12"> <h2>List of publishers, whose name contains the string 'at' at least once where string is positioned as sixteenth character from the starting:</h2> <table class='table table-bordered'> <tr> <th>Publishers name</th><th>Output</th><th>Output2</th> </tr> <?php $hostname="your_hostname"; $username="your_username"; $password="your_password"; $db = "your_dbname"; $dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password); foreach($dbh->query('SELECT pub_name,LOCATE("at",pub_name) as output, LOCATE("at",pub_name,16) as output2 FROM publisher WHERE LOCATE("at",pub_name)>0') as $row) { echo "<tr>"; echo "<td>" . $row['pub_name'] . "</td>"; echo "<td>" . $row['output'] . "</td>"; echo "<td>" . $row['output2'] . "</td>"; echo "</tr>"; } ?> </tbody></table> </div> </div> </div> </body> </html> View the example in browser
JSP script
<%@page contentType="text/html" pageEncoding="UTF-8"%> <%@ page import="java.sql.*" %> <%@ page import="java.io.*" %> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>example-locate-function</title> </head> <body> <% try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo"; Connection connection = null; Statement statement = null; ResultSet rs = null; connection = DriverManager.getConnection(Host, "root", "datasoft123"); statement = connection.createStatement(); String Data ="SELECT pub_name,LOCATE('at',pub_name) as output, LOCATE('at',pub_name,16) as output2 FROM publisher WHERE LOCATE('at',pub_name)>0"; rs = statement.executeQuery(Data); %> <TABLE border="1"> <tr width="10" bgcolor="#9979"> <td>Publishers name</td> <td>output</td> <td>output2</td> </tr> <% while (rs.next()) { %> <TR> <TD><%=rs.getString("pub_name")%></TD> <TD><%=rs.getString("output")%></TD> <TD><%=rs.getString("output2")%></TD> </TR> <% } %> </table> <% rs.close(); statement.close(); connection.close(); } catch (Exception ex) { out.println("Cant connect to database."); } %> </body> </html> All String Functions
Previous: LOAD_FILE
Next: LOWER
Mysql Query to Search a String in Database
Source: https://www.w3resource.com/mysql/string-functions/mysql-locate-function.php
0 Response to "Mysql Query to Search a String in Database"
Post a Comment