How to Select Words With Certain Values at the End of Word in SQL?
To select words with certain values at the end of the word In SQL, we can use pattern matching. A pattern matching allows users to search for certain patterns in the data. It is done using the LIKE operator in SQL. The query uses wildcard characters to match a pattern, Wildcard characters are case-sensitive.
Some wildcard characters and their meanings:
Symbol | Description |
% | specifies 0 or more characters |
_ | specifies single character |
[ ] | specifies any single character within the brackets |
^ | specifies any characters, not in the brackets |
Examples:
Symbol | Description |
a% | any value that starts with a |
%a | any value that ends with a |
a%a | any value that starts with a and ends with a |
_a% | any value which has a at the second position |
%a% | any value having a in it |
%_a% | any value having at least one character before a |
To select words with certain values at the end:
Step 1: Create a database
The database can be created using CREATE command.
Query:
CREATE DATABASE Beginner;
Step 2: Using a database
Use the below SQL statement to switch the database context to Beginner:
Query:
USE Beginner;
Step 3: Table definition
We have the following demo_table in our geek’s database.
Query:
CREATE TABLE w3wiki( FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20), GENDER VARCHAR(20));
Step 4: Inserting a data
INSERT INTO w3wiki 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: For a view a table data
Query:
SELECT * FROM w3wiki;
Output:
Step 6: Matching end character
- Query to get the last name from having ‘a’ at the end of their last name
Query:
SELECT LASTNAME FROM w3wiki WHERE LASTNAME LIKE '%a';
Output:
- Query to get Last name having ‘ra’ at the end.
Query:
SELECT LASTNAME FROM w3wiki Where LASTNAME LIKE '%ra';
Output:
- Get the last name having “A’ at the end.
Query:
SELECT LASTNAME FROM w3wiki Where LASTNAME LIKE 'A';
Output:
No value has ‘A’ at the end in the last name. This shows that values entered for pattern matching are case-sensitive.