How to sort and display mixed lists of letters and numbers as users expect?

Question

Our application has a field CustomerNumber. We have hundreds of different people using the system (each of them has his own login and his own list CustomerNumbers). An individual user can have no more than 100,000 clients. Many of them have less than 100.

Some people only put actual numbers in their customer number fields, while others use a mixture of things. The system allows you to use 20 characters, which can be AZ, 0-9 or a dash, and saves them in VARCHAR2 (20). Everything that is written in lower case is written in upper case before saving.

Now let's say we have a simple report that lists all the clients for a specific user, sorted by customer number. eg.

SELECT CustomerNumber,CustomerName
FROM Customer
WHERE User = ?
ORDER BY CustomerNumber;

This is a naive decision, because people who only ever use numbers do not want to see a simple alphabetic look (where "10" precedes "9").

I do not want to ask the user any extra questions about their data.

I am using Oracle, but I think it would be interesting to see some solutions for other databases. Indicate in which database your answer works.

What do you think is the best way to implement this?

Answer 1

In Oracle 10g:

SELECT  cust_name
FROM    t_customer c 
ORDER BY
    REGEXP_REPLACE(cust_name, '[0-9]', ''), TO_NUMBER(REGEXP_SUBSTR(cust_name, '[0-9]+'))

This will be sorted by the first value of the number, and not by its position, i. e :.

• customer1 < customer2 < customer10
  • cust1omer ? customer1
  • cust8omer1 ? cust8omer2

a ? , undefined.

.

2, REGEXP_INSTR(cust_name, '[0-9]', n) ORDER BY n , n -th (2nd, 3rd ..) .

3, TO_NUMBER(REGEXP_SUBSTR(cust_name, '[0-9]+', n)) ORDER BY n , n -th. .

, .

, , , , SORT ORDER BY , CBO - , ORDER BY .

Answer 2

, - . , 0- .

- .

, , .

Answer 3

[CustomerNumberInt], , CustomerNumber (NULL [1]),

ORDER BY CustomerNumberInt, CustomerNumber

[1] , SQL NULL ORDER BY, ( !)

Answer 4

, (SQLServer)

"" ( , , ). , , varchar, 5 . , 53,123,237,356 , T53, T123, T237, T356

UnitCode - nvarchar (30)

:

declare @sortkey nvarchar(30)

select @sortkey = 
    case
        when @unitcode like '[^0-9][0-9]%' then left(@unitcode,1) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-1)
        when @unitcode like '[^0-9][^0-9][0-9]%' then left(@unitcode,2) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-2)
        when @unitcode like '[^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,3) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-3)
        when @unitcode like '[^0-9][^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,4) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-4)
        when @unitcode like '[^0-9][^0-9][^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,5) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-5)
        when @unitcode like '%[^0-9]%' then @unitcode
        else left('000000000000000000000000000000',30-len(@unitcode)) + @unitcode
    end 

return @sortkey

I wanted to shoot myself in the face after I wrote this, however it works and doesn't seem to kill the server when it works.

Answer 5

I used this in SQL SERVER and worked fine: here the solution is to populate the numeric values ​​with the character in front so that everyone has the same string length.

Here is an example of using this approach:

select MyCol
from MyTable
order by 
    case IsNumeric(MyCol) 
        when 1 then Replicate('0', 100 - Len(MyCol)) + MyCol
        else MyCol
    end

The value 100 must be replaced by the actual length of this column.