Sort data in an Access database with numbers and letters in a column

Question

Please help me because I couldn’t get it right.

What is SQL access to select this column (columnA) so that it returns a set of results with different values, sorted first by numbers and then by letters.

Here are the column values: {10A, 9C, 12D, 11G, 9B, 10C, 9R, 8T}

I tried 'Select a single ColumnA from tblClass order by 1' but it returns {10A, 10C, 11G, 12D, 8T, 9B, 9C, 9R}, which is not what I want.

Thanks in advance.

Answer 1

You can use the Val () function for this. In the help section: "The Val function stops reading a line with the first character that it cannot recognize as part of a number"

Val (10A) will give you 10, Val (9C) will give you 9, etc. Therefore, in your request, Val (ColumnA) is first ordered, then ColumnA.

SELECT DISTINCT Val([ColumnA]) AS number_part, ColumnA FROM tblClass ORDER BY Val([ColumnA]), ColumnA; 

Answer 2

 SELECT DISTINCT ColumnA FROM tblClass ORDER BY CInt(LEFT(ColumnA,len(ColumnA)-1)), RIGHT(ColumnA,1); 

If the last character is a letter and the rest is a number.

Answer 3

A data type is a string that sorts correctly to get the result you want, to divide your values ​​into numeric and alphabetical parts, and then sort first by numeric and then alphabetically. Not being an Access programmer, I cannot help you with how you are going to do this.

Answer 4

order of 1?

You do not mean order ColumnA?

 SELECT DISTINCT ColumnA FROM tblClass ORDER BY ColumnA