MySQL query to display the first alphabet from strings in a separate column

MySQLi DatabaseMySQL

To fetch the first alphabet from the strings, use LEFT(). This method allows you to return characters from the left of the string.

Let us first see an example and create a table −

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstName varchar(100)
);
Query OK, 0 rows affected (1.03 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(FirstName) values('John');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(FirstName) values('Adam');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable(FirstName) values('Jace');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable(FirstName) values('Chris');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable(FirstName) values('Bob');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(FirstName) values('Carol');
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+-----------+
| Id | FirstName |
+----+-----------+
|  1 | John      |
|  2 | Adam      |
|  3 | Jace      |
|  4 | Chris     |
|  5 | Bob       |
|  6 | Carol     |
+----+-----------+
6 rows in set (0.00 sec)

Following is the query to display the first alphabet from the column with string values. The result is displayed in a new column −

mysql> select Id,FirstName,upper(left(FirstName,1)) AS FirstLetter from DemoTable order by FirstName;

This will produce the following output −

+----+-----------+-------------+
| Id | FirstName | FirstLetter |
+----+-----------+-------------+
|  2 | Adam      | A           |
|  5 | Bob       | B           |
|  6 | Carol     | C           |
|  4 | Chris     | C           |
|  3 | Jace      | J           |
|  1 | John      | J           |
+----+-----------+-------------+
6 rows in set (0.03 sec)
raja
Published on 01-Oct-2019 08:06:35
Advertisements