How to Select Words With Certain Values at the End of Word in SQL?

Selecting words from a string with specific values at the end can be crucial in SQL for tasks like filtering data, pattern matching, and extracting relevant information. In SQL, we often need to work with strings where we need to find words ending with certain characters or patterns.

This guide will show us how to use functions and operators effectively to achieve this, making it easier for both beginners and advanced users to perform complex queries.

Some Wildcard Characters and Their Meanings

In SQL, wildcard characters are used to match patterns in text fields. They help in filtering and selecting data based on patterns rather than exact matches. Understanding these wildcard characters—such as %, _, [ ], and ^ is crucial for effective querying and pattern matching within strings. Each symbol represents different types of matches, enabling flexible and powerful SQL queries.

Examples:

Steps to Select Words with Certain Values at the End

To select words from a string with specific values at the end in SQL, follow these steps. By using the LIKE operator combined with wildcard characters, we can filter and retrieve data based on specific patterns in text fields. This process is essential for tasks like filtering data, pattern matching, and extracting specific information from strings.

Step 1:  Table definition

First, we need to set up a table with sample data to work with. In this example, we use a geeksforgeeks table in a geeks database to demonstrate the process:

Query:

CREATE TABLE geeksforgeeks(
FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20),
GENDER VARCHAR(20));

Step 2: Inserting a data

Populate the table with sample data that includes names and gender information. This will allow us to demonstrate different queries using the LIKE operator:

Query:

INSERT INTO geeksforgeeks VALUES
('ROMY', 'Kumari', 'female'), 
('Rinkle', 'Arora', 'female'),
('Nikhil', 'Kalra','male'),
 ('Pushkar', 'Jha', 'male'),
('Sujata', 'jha', 'female'),
 ('Roshini', 'Kumari','female'),
('Ayushi', 'Chaudhary', 'female'),
 ('Akash', 'Gupta', 'male'),
('Akanksha', 'Gupta', 'female'), 
('Chiranjeev', 'Arya', 'male'),
('Shivani', 'Jindal','female'),
 ('Shalini', 'Jha', 'female'),
('Sambhavi','Jha', 'female');

Step 5: Viewing Table Data

To see the data in the geeksforgeeks table, use the SELECT statement. This command will display all columns and rows in the geeksforgeeks table.

Query:

SELECT * FROM geeksforgeeks;

Output

Step 6: Matching End Character

To select words with specific values at the end of a word, use the LIKE operator with patterns. For example, we can use %a to find words ending with 'a', or %ra to find words ending with 'ra'. These patterns enable flexible searches within text fields, helping us filter data based on specific criteria.

1. Query to get the last name from having 'a' at the end of their last name

The query retrieves the LASTNAME column from the geeksforgeeks table where the LASTNAME ends with the letter 'a'. The % character is used as a wildcard to match any sequence of characters, allowing us to filter names that end with 'a'.

Query:

SELECT LASTNAME FROM geeksforgeeks WHERE LASTNAME LIKE '%a';

Output

2. Query to get last names ending with 'ra'

The query retrieves last names from the geeksforgeeks table that end with the substring 'ra'. The % wildcard is used to match any characters before 'b', making it possible to filter names ending specifically with 'ra'.

Query:

SELECT LASTNAME FROM geeksforgeeks Where LASTNAME LIKE '%ra'

Output

3. Query to get last names ending with 'A':

This query selects last names from the geeksforgeeks table that end with the letter 'A'. The result will show that there are no last names ending with 'A', demonstrating that SQL pattern matching is case-sensitive.

Query:

SELECT LASTNAME FROM geeksforgeeks Where LASTNAME LIKE 'A';

Output

Conclusion

Selecting words with certain values at the end in SQL can be effectively achieved using the LIKE operator with wildcards, the RIGHT function for precise character selection from the end, and the SUBSTRING function for substring comparisons. Each of these methods provides a powerful way to filter and extract specific patterns within strings, enabling more complex data manipulation in SQL. By mastering these techniques, we can improve our SQL queries and make our database interactions more efficient.

romy421kumari

Follow

Improve

Article Tags :

• SQL
• Blogathon
• Databases
• Blogathon-2021