Order by last three characters in sql

SQL stands for Structured Query Language. It is used to communicate with the database. There are some standard SQL commands like ‘select’, ‘delete’, ‘alter’ etc. To delete the last N characters from the field we will use the string function.

Table of Contents Show

String function:
How do I sort last 3 characters in SQL?
What is ORDER BY 3 in SQL?
How do I select the first 3 characters in SQL?
How do I get last 5 characters of a string in SQL?

String function:

It is used to perform an operation on an input string and return an output string. There are various string functions like LEN(for SQL server), SUBSTR, LTRIM, TRIM, etc.

To perform the required function we need the following functions:

1. SUBSTRING(): This function is used to find a sub-string from the string from the given position. It takes three parameters:

String: It is a required parameter. It provides information about the string on which function is applied.
Start: It gives the starting position of the string. It is also the required parameter.
Length: It is an optional parameter. By default, it takes the length of the whole string.

Query:

SUBSTRING('geeksforgeeks', 1, 5);

Output:

geeks

2. LEN(): This syntax is not the standard one. For different server syntax for returning the length of a string may vary. For example, LEN() is in SQL server, LENGTH() is used in oracle database, and so on.

It takes only one parameter that is the string whose length you need to find.

Query:

LEN('geeksforgeeks')

Output:

To delete the last N characters from the field we will use the following query:

Query:

SUBSTRING(string, 1, length(string)-N)

Here, string denotes the field, 1 denotes the starting position of string, and length(string)-N denotes the length of the string. For the purpose of demonstration, we will be creating a geeksforgeeks table in a database called “geeks“.

Creating the Database:

Use the below SQL statement to create a database called geeks:

CREATE DATABASE geeks;

Using the Database:

Use the below SQL statement to switch the database context to geeks:

USE geeks;

Table definition: We have the following geeksforgeeks table in our geek’s database.

Query:

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

Adding data to the TABLE: Use the below statement to add data to the geeksforgeeks table:

Query:

INSERT INTO geeksforgeeks VALUES ('ROMY', 'Kumari', 'New Delhi', 22, 'female');
INSERT INTO geeksforgeeks VALUES ('Pushkar', 'jha', 'New Delhi', 23, 'male');
INSERT INTO geeksforgeeks VALUES ('Sujata', 'jha', 'Bihar', 30, 'female');
INSERT INTO geeksforgeeks VALUES ('Roshini', 'Kumari', 'Bihar', 16, 'female');
INSERT INTO geeksforgeeks VALUES ('Avinav', 'Pandey', 'New Delhi', 21, 'male');

To see the content of the table: Use the below command to see the content of the geeksforgeeks table:

Query:

SELECT * FROM geeksforgeeks;

Now to delete the last N characters from the field we will use the geeksforgeeks table. Below is the syntax for the SUBSTRING() function to delete the last N characters from the field.

Syntax:

SELECT SUBSTRING(column_name,1,length(column_name)-N) FROM table_name;

Example: Delete the last 2 characters from the FIRSTNAME column from the geeksforgeeks table.

Query:

SELECT SUBSTRING(FIRSTNAME,1,len(FIRSTNAME)-2) FROM geeks for geeks;

Output:

Delete the last 3 characters from the country column and then view it.

Query:

SELECT FIRSTNAME AS BEFORE, SUBSTRING(FIRSTNAME,1,len(FIRSTNAME)-3)AS AFTER FROM geeksforgeeks;

Output:

Problem:

You want to display records from a table in alphabetical or reverse-alphabetical order according to given column.

Example:

Our database has a table named customer. The customer table contains data in the id, first_name, and last_name columns.

id	first_name	last_name
1	Susan	Thomas
2	John	Michael
3	Tom	Muller

Let’s display each customer’s information, sorted in ascending order by their last name.

Solution:

SELECT id,  
    first_name,
    last_name, 
  FROM customer
  ORDER BY last_name ASC;

This query returns sorted alphabetically records:

id	first_name	last_name
2	John	Michael
3	Tom	Muller
1	Susan	Thomas

Discussion:

If you want to select records from a table but would like to see them sorted according to a given column, you can simply use the ORDER BY clause at the end of a SELECT statement. It doesn’t matter how complicated or long your SQL query is—ORDER BY should always be at the end of the command.

After the ORDER BY keyword, you name the column by which the records should be sorted. In our query, we sort by the last name of the customer.

By default, ORDER BY without any additional specifier sorts in ascending order (equivalent to using the ASC keyword explicitly). As you can probably guess, ASC stands for “ascending.” If you’d like to sort in descending order, simplify specify the DESC keyword after the column name.

The query below is similar to the previous but returns a list of customers sorted in descending order by their last name:

  SELECT id,  
    first_name,
    last_name, 
  FROM customer
  ORDER BY last_name DESC;

id	first_name	last_name
1	Susan	Thomas
3	Tom	Muller
2	John	Michael

Subscribe to our newsletter Join our monthly newsletter to be
notified about the latest posts.

Email address

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

How do I sort last 3 characters in SQL?

SELECT *FROM yourTableName ORDER BY RIGHT(yourColumnName,3) yourSortingOrder; Just replace the 'yourSortingOrder' to ASC or DESC to set the ascending or descending order respectively. Here is the query to order by last 3 chars. Case 1 − Get the result in ascending order.

What is ORDER BY 3 in SQL?

Order by 3 DESC. In this query, column birthdate is at the 3rd position; therefore, we can use three in the Order by clause to sort results on this column data. Note: I would not recommend using column position in Order By clause. You should always use a column name in Order by clause.

How do I select the first 3 characters in SQL?

SELECT LEN(column_name) FROM table_name; And you can use SUBSTRING or SUBSTR() function go get first three characters of a column.

How do I get last 5 characters of a string in SQL?

Learn MySQL from scratch for Data Science and Analytics To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.