MySQL provides the RIGHT function to select a number of characters starting from the right of the string. In this article, we will learn how to use right with MySQL.
The basic syntax of a RIGHT is as follows:
SELECT RIGHT(string_name, length);
We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called
docker-compose.yml and add the following.
version: '3' services: db: image: mysql:latest container_name: db environment: MYSQL_ROOT_PASSWORD: root_pass MYSQL_DATABASE: app_db MYSQL_USER: db_user MYSQL_PASSWORD: db_user_pass ports: - "6033:3306" volumes: - dbdata:/var/lib/mysql phpmyadmin: image: phpmyadmin/phpmyadmin container_name: pma links: - db environment: PMA_HOST: db PMA_PORT: 3306 PMA_ARBITRARY: 1 restart: always ports: - 8081:80 volumes: dbdata:
Now, navigate to
http://localhost:8081/ to access phpMyAdmin. Then log in with the username
root and pass
Click the SQL tab and you are ready to go.
The basic example is straight forward. We can pass a string into the Right function with the number of characters we would like to select.
SELECT RIGHT('Hello World!', 6)
|RIGHT('Hello World!', 6)|
We can also use MySql variables with RIGHT
SET @str = 'Hello World!'; SELECT RIGHT(@str, 6);
That's the gist of using the right function. We can also use RIGHT on a table. If you would like to try that, let's first set up a table.
In this article, we will need some data to work with. We will be using the sample db provided here: https://dev.mysql.com/doc/employee/en/. However, we will only enter what we need rather than import the whole db.
With the SQL tab open (or your own sql cli going), let's first create our DB and select it.
create DATABASE if not EXISTS sakila; USE sakila;
CREATE TABLE employees ( emp_no INT NOT NULL, birth_date DATE NOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender VARCHAR(16) NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY (emp_no) );
Now, let's enter a few rows
INSERT INTO employees VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'), (10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'), (10003,'1959-12-03','Parto','Bamford','M','1986-08-28'), (10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'), (10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
Now that we are set up, we can use RIGHT on our columns.
SELECT RIGHT(first_name, 5) FROM employees;