w3resource
MYSQL Tutorial

MySQL CHARACTER_LENGTH() function

CHARACTER_LENGTH()

MySQL CHARACTER_LENGTH() returns the length of a given string. The length is measured in characters. The CHARACTER_LENGTH() is the synonym of CHAR_LENGTH().

Syntax:

 
CHARACTER_LENGTH (str)

Argument

Name Description
str A string whose length is to be retrieved.

Syntax Diagram:

MySQL CHARACTER_LENGTH() Function - Syntax Diagram

MySQL Version: 5.6

Video Presentation

Example : MySQL CHARACTER_LENGTH() function

The following MySQL statement counts the length of the string specified as an argument.

Code:

SELECT CHARACTER_LENGTH('test string');

Sample Output:

mysql> SELECT CHARACTER_LENGTH('test string');
+---------------------------------+
| CHARACTER_LENGTH('test string') |
+---------------------------------+
|                              11 | 
+---------------------------------+
1 row in set (0.00 sec)

Example of MySQL character_length() function where clause

The following MySQL statement counts only those of the publisher's names (from column pub_name of the publisher table) which are more than 20 characters long.

Code:

SELECT pub_name,CHARACTER_LENGTH(pub_name)
AS 'character length'
FROM publisher
WHERE CHARACTER_LENGTH(pub_name)>20;

Sample table: publisher


Sample Output:

mysql> SELECT pub_name,CHARACTER_LENGTH(pub_name)
    -> AS 'character length'
    -> FROM publisher
    -> WHERE CHARACTER_LENGTH(pub_name)>20;
+------------------------------+------------------+
| pub_name                     | character length |
+------------------------------+------------------+
| New Harrold Publication      |               23 | 
| Summer Night Publication     |               24 | 
| Pieterson Grp. of Publishers |               28 | 
+------------------------------+------------------+
3 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>example-character_length-function - php mysql examples | w3resource</title>
<meta name="description" content="example-character_length-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>A list of Publishers those who have more than twenty characters in their name. Second column shows the number of characters in the name of the Publisher:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's Name</th><th>Character length</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,CHARACTER_LENGTH(pub_name)
AS "character length" 

FROM publisher
WHERE CHARACTER_LENGTH(pub_name)>20') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['character length'] . "</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-character_length-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,CHARACTER_LENGTH(pub_name) AS 'character length' FROM publisher WHERE CHARACTER_LENGTH(pub_name)>20";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publisher's Name</td>
<td>Character length</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("pub_name")%></TD>
<TD><%=rs.getString("character length")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>
</body>
</html>

All String Functions

MySQL String Functions, slide presentation

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.