- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Sort records with special characters like '2321/78/54-6'
To sort, use ORDER BY SUBSTRING(). Let us first create a table −
mysql> create table DemoTable -> ( -> Value varchar(40) -> ); Query OK, 0 rows affected (0.59 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values('2321/78/54-6') -> ; Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2321/78/54-8'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('2321/78/54-5'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('2321/78/54-9'); Query OK, 1 row affected (0.21 sec)
Display all records from the table using select statement −
mysql> select * from DemoTable;
This will produce the following output −
+--------------+ | Value | +--------------+ | 2321/78/54-6 | | 2321/78/54-8 | | 2321/78/54-5 | | 2321/78/54-9 | +--------------+ 4 rows in set (0.00 sec)
Here is the query to sort records with special characters −
mysql> select * from DemoTable -> where Value like '2321/78/54%' -> order by substring(Value from 1 for 10),cast(substring(Value from 11) as decimal);
This will produce the following output −
+--------------+ | Value | +--------------+ | 2321/78/54-9 | | 2321/78/54-8 | | 2321/78/54-6 | | 2321/78/54-5 | +--------------+ 4 rows in set (0.05 sec)
- Related Articles
- How I can use a database-name with special characters like " customer_tracker-990" in MongoDB console
- How to delete records based on a word with underscore like MONTH_JAN'?
- Delete a collection from MongoDB with special characters?
- Finding matching records with LIKE in MongoDB?
- Special Characters in HTML
- MySQL LIKE command doesn't work with strings containing dots to display records beginning with a specific number
- Search a string with special characters in a MongoDB document?
- MySQL query to select a specific string with special characters
- How to use special characters in column names with MySQL?
- Find the records with % character in a LIKE query with MySQL
- MySQL query to retrieve only the column values with special characters?
- Update the records in a table with a specific year fetched from date format like '10/12/2010'?
- How to separate strings in R that are joined with special characters?
- Replacing all special characters with their ASCII value in a string - JavaScript
- How to sort strings with accented characters using JavaScript?

Advertisements