Geek asks and answers

ORDERBY "human" alphabetical order using SQL string manipulation

I have a message table with headers that are in the "human" alphabetical order, but not in the alphabetical order in the computer. They are presented in two versions: digital and alphabetical:

  • Numerical: Figure 1.9, Figure 1.10, Figure 1.11 ...

  • Alphabetical text: Figure 1A ... Figure 1Z ... Figure 1AA

If I orderbytitle, the result is that 1.10-1.19 are between 1.1 and 1.2, and 1AA-1AZ is between 1A and 1B. But that is not what I want; I want a “human” alphabetical order in which 1.10 comes after 1.9 and 1AA comes after 1Z.

I am wondering if there is another SQL way to get the order that I want to use with string manipulations (or something else that I did not think about).

I am not an expert in SQL, so I do not know if this is possible, but if there was a way to make a conditional replacement, it seems that I could put things in order, I want to do this:

  • delete a period (which can be done with replace, right?)

  • if the remaining number of digits is more than three characters, add 0(zero) after the first character.

This, it would seem, will give me the result that I want: it 1.9will become 109, which precedes 110; 1Zwill become 10Zthat precedes 1AA. But can this be done in SQL? If so, what will be the syntax?

, - .

Wordpress, , SQL-, (, ) , MySQL, PHP.

+5
2

- , .

1) 2) , , (, 1.1 AAA - ).

... . .

0

, , ,

Alter FUNCTION [dbo].[GetHumanSortOrder] (@ColumnName VARCHAR(50))
RETURNS VARCHAR(20)
AS

BEGIN
DECLARE @HumanSortOrder VARCHAR(20)

SELECT  @HumanSortOrder =
CASE 
    WHEN (LEN(replace(replace(<Column_Name>,'.',''),'Figure ',''))) = 2
THEN
CONCAT (SUBSTRING(replace(replace(<Column_Name>,'.',''),'Figure ',''),1,1),'0',SUBSTRING(replace(replace(<Column_Name>,'.',''),'Figure ',''),2,2))
    ELSE
       replace(replace(<Column_Name>,'.',''),'Figure ','')
      END 
FROM    <Table_Name> AS a (NOLOCK)
WHERE   <Column_Name> = @ColumnName

RETURN  @HumanSortOrder

END

, , 104,107,119,10A, 10B ..

 SELECT * FROM <Table_Name> ORDER BY GetHumanSortOrder(<Column_Name>)

,

0

More articles:


All Articles