如何从SQL中对非数字数据进行排序

Question

我在SQL中排序有问题。

数据：

ID   accountCode
1     A99
2     A3792
3     A230
4     A2
5     AA2
6     AB23
7     EXMPLECODE

查询：

select top 1 accountCode
from AccountCodes
where accountCode like 'A%'
order by accountCode desc

结果：

A99

预期结果：

A379

我怎样才能在这个研究中得到A379的结果？

感谢的帮助

Answer 1

将子字符串转换为数字，进行强制转换，然后对进行排序

select top 1 accountCode
from AccountCodes
where accountCode like 'A%'
order by CAST(SUBSTRING(accountCode, 2) AS INT) desc

Answer 2

您首先需要删除所有非数字字符，然后将其强制转换为整数

CREATE TABLE AccountCodes
([ID] int, [accountCode] varchar(4))
;

INSERT INTO AccountCodes
([ID], [accountCode])
VALUES
(1, 'A99'),
(2, 'A379'),
(3, 'A230')
;

3 rows affected

select top 1 accountCode
from AccountCodes
where accountCode like 'A%'
order by CAST(stuff(accountCode, 1, patindex('%[0-9]%', accountCode)-1, '') as INT) desc

accountCode
A379

假设#AccountCodes是您的真实表，

CREATE TABLE #AccountCodes
( [accountCode] varchar(30))
;

INSERT INTO #AccountCodes
([accountCode])
VALUES
( 'A99'),
( 'A379'),
( 'A230'),
('A2323'),
('A23'),
('A21333'),
('AB23'),
('EXMPLECODE')
;

现在创建一个#临时表

CREATE TABLE #temp  (ID int identity(1,1) primary key, [accountCode] varchar(30))
insert into #temp([accountCode])
select  accountcode from
(
select *
,cast(substring(accountcode,(PATINDEX('%[0-9]%',accountcode)),len(accountcode)) as int)newCode
from #AccountCodes
where PATINDEX('%[0-9]%',accountcode)>0
)t4
order by newCode
insert into #temp([accountCode])
select [accountCode]
from #AccountCodes
where PATINDEX('%[0-9]%',accountcode)<=0
order by [accountCode]

select * from #temp order by id
drop table #AccountCodes,#temp

你可以把样本数据扔到不工作的地方

我们从accountCode中提取所有数字，按数字长度排序，然后按数字排序。

select   top 1 accountCode
from     t
where    accountCode like 'A%'
order by len(substring(accountCode, PatIndex('%[0-9]%', accountCode), len(accountCode))) desc, substring(accountCode, PatIndex('%[0-9]%', accountCode), len(accountCode)) desc

accountCode
A379

在您的查询中，您选择了排名前1的结果，其中accountCode类似于"A%"；这显然将选择第一个结果：A99。你要么把它改成"；A___%"；或子串和排序。