SQL Query to Find the Name of a Person Whose Name Starts with Specific Letter

When we work with databases, we often need to search for the name of a Person whose name starts with a specific Letter. We can also find the names of persons whose name starts with a specific letter using SQL. This is possible by using the 'LIKE' operator in SQL, it allows us to do pattern matching on a text.

Prerequisites:

The following are the prerequisites to understand this tutorial -

• Basic SQL syntax understanding.
• A database management tool like MySQL, Oracle SQL or SQLite.

What is 'LIKE' Operator?

The 'LIKE' operator in SQL is used to search for a specific pattern in a column, it is used with wildcard characters which are:

• % : This represents, zero , one or multiple characters.
• _ : This represents a single character.

For Example,

• 'A%' will match any string that starts with the letter 'A'.
• '_s%' will match any string that has the second letter 's' and any no. of letters after s.
• '%i_' will match any string that has the second last letter 'i' and any no. of letters before it. 

SQL Query Syntax:

Implementation of finding the name of a person whose name starts with specific letter is as shown below:

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern';

Here,

• Select cloumn_name: This specifies the column we want to search in.
• From table_name: This specifies the table name in which we want to search for the pattern.
• WHERE column_name LIKE 'A%': It filters the results to show only rows which have 'column_name' starting with the letter 'A'.

First, we will create a table insert some values, and then learn the main concept.

Creating Table:

CREATE TABLE persons (
   id INT PRIMARY KEY,
   name VARCHAR(50)
);

Inserting Values:

INSERT INTO persons (id, name) VALUES (1, 'Ali');
INSERT INTO persons (id, name) VALUES (2, 'Bharat');
INSERT INTO persons (id, name) VALUES (4, 'Asif');
INSERT INTO persons (id, name) VALUES (5, 'Rohit');
INSERT INTO persons (id, name) VALUES (6, 'Arjun');

Now, let's implement the main concept where we will name of a person whose name starts with specific letter in SQL.

Example 1

Let's say we want to find the name of a person whose name starts with the letter 'A'. To achieve this, we will use 'LIKE' Operator with the pattern 'A%'.

SELECT name from persons where name LIKE 'A%';

Following is the output obtained:

+-------+
| name  |
+-------+
| Ali   |
| Asif  |
| Arjun |
+-------+

Here, we can observe that it returned all the names starting with the letter 'A' in the person table.

Example 2

Now, let's find the name of the person in the table whose name starts with the letter 'B'.

SELECT name FROM persons WHERE name LIKE 'B%';

The output of the above code is as shown below:

+--------+
| name   |
+--------+
| Bharat |
+--------+

Example 3

Now, let's search for the name of a person whose name's second letter is 's'.

To achieve this, we need to use the wildcard character _. We can search for a person's name in the person table whose name's second letter is 's' by using this pattern _s%:

SELECT name FROM persons WHERE name LIKE '_s%';

Following is the output of the above code:

+------+
| name |
+------+
| Asif |
+------+

Example 4

Now, let's search for the name of a person whose name's second last letter is 'i'. To achieve this, we need to use the wildcard character '_'. We can search for a person's name in the person table whose name's second last letter is 'i' by using this pattern '%i_'.

SELECT name FROM persons WHERE name LIKE '%i_';

We get the output as shown below:

+-------+
| name  |
+-------+
| Asif  |
| Rohit |
+-------+