Problem: Change the text-value of a specific column of a mysql table into 'Title Case'
MySQL doesn't support any function for Title-Case conversion.If the text-values of the column are all of single-word then the conversion can be done using a combination of the functions 'UPPER', 'LOWER', 'SUBSTRING' etc as suggested in this StackOverflow answer by Anupam.
But it will not work If the text-values are of multiple words.
1. Stored Procedures/Functions can be one solution as suggested in the answers of this StackOverflow question.
2. Writing server side scripts (JSP, Python, PHP etc.) can be another solution.
A PHP solution can be found at this post "CONVERTING TO TITLE CASE IN MYSQL – CAPITALIZING MYSQL (A PHP SOLUTION)".
I am showing here how we can write a JSP script to do this.
First we create our MySQL table and insert data into it:
CREATE TABLE `test`.`Employee` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`fullName` VARCHAR(45) NULL ,
`designation` VARCHAR(45) NULL ,
PRIMARY KEY (`id`)
);
insert into Employee
(fullName, designation)
values
('ALBERT NASIMSTEIN', 'CUSTOMER RELATIVITY OFFICER'),
('SIR ISSAC NASIMTON', 'APPLEICATION MANAGER'),
('LOUISE PASTEUR', 'MILK PASTEURISATION MANAGER'),
('GALILEO', 'ASTRONOMICAL OFFICER'),
('NIKOLA TESLA', 'ELECTRO MAGNETIC FIELD OFFICER'),
('ARISTOTLE', 'CHEIF EXECUTIVE OFFICER');
It will result in the following table:
| id | fullName | designation |
|---|---|---|
| 1 | ALBERT NASIMSTEIN | CUSTOMER RELATIVITY OFFICER |
| 2 | SIR ISSAC NASIMTON | APPLEICATION MANAGER |
| 3 | LOUISE PASTEUR | MILK PASTEURISATION MANAGER |
| 4 | GALILEO | ASTRONOMICAL OFFICER |
| 5 | NIKOLA TESLA | ELECTRO MAGNETIC FIELD OFFICER |
| 6 | ARISTOTLE | CHEIF EXECUTIVE OFFICER |
Now, We can write the JSP file, convert_to_titlecase.jsp, like this:
<%@page import="java.sql.*"%>
<%@page import="org.apache.commons.lang3.text.WordUtils"%>
<%@page contentType="text/html;charset=ISO-8859-15" language="java" %>
<%
response.setHeader("Cache-Control","no-cache");
response.setHeader("Pragma","no-cache");
response.setDateHeader ("Expires", 0);
Statement stmt = null;
Connection conn = null;
// Set response content type
response.setContentType("text/html");
String title = "Title Case Conversion";
String docType = "\n";
out.println(docType + "\n" + "" + title
+ " \n" + "\n"
+ "" + title + "
\n");
try {
// Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
String dbName = request.getParameter("dbName");
// Open a connection
conn = DriverManager.getConnection("jdbc:mysql://localhost/" + dbName);
// Execute SQL query
stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String tableName = request.getParameter("tableName");
String columnName = request.getParameter("columnName");
out.println("Database: " + dbName + "
");
out.println("Table: " + tableName + "
");
out.println("Column: " + columnName);
out.println("
");
String sql;
sql = "SELECT * FROM " + tableName;
ResultSet rs = stmt.executeQuery(sql);
long countColumn = 0;
long countFail = 0;
while (rs.next()) {
String columnValue = rs.getString(columnName);
try {
rs.updateString(columnName, WordUtils.capitalizeFully(columnValue) );
rs.updateRow();
} catch(Exception exc) {
out.println("" + columnValue + " not updated; " + exc.getMessage() + "");
countFail++;
continue;
}
countColumn++;
}
out.println(countColumn + " columns updated successfully.
");
out.println(countFail + " failed.");
out.println("");
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}// nothing we can do
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}// end finally try
} // end try
%>
On Apache Tomcat server we can test it locally by calling this script by passing the parameters dbName, tableName and columnName as follows:
http://localhost:8080/myproject/convert_to_titlecase.jsp?dbName=test&tableName=Employee&columnName=designation
It will change all the texts of designation column to Title-Case as follows:
| id | fullName | designation |
|---|---|---|
| 1 | ALBERT NASIMSTEIN | Customer Relativity Officer |
| 2 | SIR ISSAC NASIMTON | Appleication Manager |
| 3 | LOUISE PASTEUR | Milk Pasteurisation Manager |
| 4 | GALILEO | Astronomical Officer |
| 5 | NIKOLA TESLA | Electro Magnetic Field Officer |
| 6 | ARISTOTLE | Cheif Executive Officer |
