MySQL change column text to Title Case / Proper Case / Camel Case

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" + "\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