Home > Database > Mysql Tutorial > How Can I Perform Case-Insensitive Sorting in SQL's ORDER BY Clause?

How Can I Perform Case-Insensitive Sorting in SQL's ORDER BY Clause?

Mary-Kate Olsen
Release: 2024-12-30 14:22:11
Original
360 people have browsed it

How Can I Perform Case-Insensitive Sorting in SQL's ORDER BY Clause?

Sorting SQL Results Case Insensitively with the Order By Statement

When sorting data with SQL's Order By statement, case sensitivity can interfere with the desired ordering. For example, if a SQLite table contains values such as "A", "a", "B", and "T", the default sorting behavior will result in:

A
B
C
T
a
b
c
g
Copy after login

To ensure case-insensitive sorting, the COLLATE keyword can be used in conjunction with the Order By statement. By specifying COLLATE NOCASE, the database will disregard case differences when comparing values.

SELECT * FROM NOTES ORDER BY title COLLATE NOCASE
Copy after login

This will return a sorted result set in which both uppercase and lowercase characters have equal weighting:

A
a
b
B
C
c
g
T
Copy after login

Additionally, the ASC or DESC keywords can be used to control the sort order (ascending or descending) by adding them after COLLATE NOCASE:

ORDER BY TITLE COLLATE NOCASE ASC -- Sort ascending (A-Z, a-z)
Copy after login
ORDER BY TITLE COLLATE NOCASE DESC -- Sort descending (Z-A, z-a)
Copy after login

The above is the detailed content of How Can I Perform Case-Insensitive Sorting in SQL's ORDER BY Clause?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template