SQL中的字母数字排序 [英] Alpha Numeric sort in sql

问题描述

我目前正在做一个项目，其中数据库需要对批号进行排序
前缀是nvarchar
lotnum是int
后缀是nvarchar

我设法转换了批号
我使用的代码是

Hi i am currently doing a project in which the database needs to sort the lot numbers
prefix is nvarchar
lotnum is int
suffix is nvarchar

i have managed to convert the lot number
code i used is

Select (case when prefix is null then '' else prefix end) +
CONVERT ( nvarchar , ( lotnumber ) ) +(case when suffix is null then '' else suffix end)

(数据库中的值为a1a，1a，1,2,100)
当我按手数订购时，我得到

(values in the database are a1a,1a,1,2,100)
when i order by lotnumber i get

a1a
1a
1
2
100

然后在订单前加上
并得到这个结果

then prefix to the order by
and get this result

1
a1a
1a
2
100

我也添加了后缀，并返回相同的结果

我需要按以下顺序订购

i have added the suffix as well and returns the same result

i need to order it as follows

1
1a
2
100
a1a

请有人帮我解决这个

Please could someone help me on this

推荐答案

在Sql中排序字母数字数据 [在sql中进行字母数字排序 [

How to Sort Alphanumeric Data in SQL[^]
Sort AlphaNumeric Data in Sql[^]
Alphanumeric sorting in sql[^]

这里有两个示例可供您使用:

处理DataGrid
http://bea.stollnitz.com/blog/?p=426 [ http://bea.stollnitz.com/blog/?p=24 [

HEre are two examples for you:

Dealing with DataGrid
http://bea.stollnitz.com/blog/?p=426[^]

Alfanumeric custom sort
http://bea.stollnitz.com/blog/?p=24[^]

这篇关于SQL中的字母数字排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！