What is considered when ordering a varchar field? Being filled by numbers

Question

What is considered when ordering a varchar field? Being filled by numbers

Navigation

#1 by (4 votes)
#2 by (2 votes)

5

I have a table with the birthdays of the month, for a reason of use here we should store this as varchar . In the use of it it is necessary to sort by the date from the most recent to the oldest, it used this command:

SELECT * FROM m_aniversariantes order by dia;

And we got this result:

To solve you have to do this other select:

SELECT * FROM m_aniversariantes order by CAST(dia AS INT);

That returned the way we needed.

My question is, what did the first SELECT consider to sort the list?

sql select

asked by anonymous 07.05.2018 / 18:41

2 answers

2

It considers as an alphabetical reading, taking the first value, as "1" and "2" comes before the "3" it considers them first, since it is a text field and does not import the total value and yes character by character.

07.05.2018 / 19:51

CSS: float vs. inline-block. What are the advantages and disadvantages of each? R - Map of Brazilian cities

score 4 · Accepted Answer

The data has been sorted as text (alphanumeric sorting) ascending, as is the data type of the column. It's the familiar alphabetical order.

There are algorithms that optimize the ordering process, but for practical purposes it is done by comparing the characters from the leftmost character to the corresponding position character in the other evaluated records. Next, the next character is evaluated, and this cycle is successively followed by all characters (use the largest content as a reference).

In this way, in the day-to-day common lists, the algorithm causes Adriana to appear in the call lists before Ana , for example. Even the second having only 3 letters, the 2nd character of Ana ( n >> U+006E ) is greater * than the second character of Adriana ( d >> U+0064 ). If in this list (and if it were possible to register someone like that) had someone called 1berto , it would appear before Adriana , inclusive.

* Based on the Unicode Table character codes. There may be variations in each bank, depending on how the accented characters are handled, the data encoding, case handling and so on.

In this arrangement, common special numbers and characters always precede the letters in the precedence question, as you can see in the ASCII < a> or UNICODE .

This causes a counterintuitive effect when we are dealing only with numbers, as is your case, for 489 will always appear before 75 (because 4 (U+0034) is less than 7 (U+0037) ), for example, and so on.

I hope I have helped.