How can I sort my non numeric data from SQL?

FY STUDIO 0
Viewed 80

I have problem with sorting in SQL.

Data:

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

Query:

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

Result:

A99

Expected Result:

A379

How can I get A379 result in this stuation?

Thank you for help

sql-server sql
5 Answers

substring into number, cast, then sort

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

You first need to remove the all non numeric characters and then cast it as integer

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

fiddle

nbk0

Suppose #AccountCodes is your real table,

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

Now create one #temp table

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

You can throw sample data where it not working

KumarHarsh0

we're extracting all the numbers from accountCode, ordering by the length of the number and then by the digits.

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

Fiddle

DannySlor0

In your query you're selecting the top 1 result where the accountCode is like "A%" which is obviously going to pick the first result: A99. You either have to change it to "A___%" or substring and sort.

EmilioW0
Related