How can I sort my non numeric data from sql?-CodePudding

Hello I have problem with sorting in sql.

MY DATA :

ID   accountCode
1     A99
2     A379
3     A230

QUERY :

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

RESULT :

A99

MY EXPECTATION :

A379

How can i get A379 result in this stuation ?

Thank you for help

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

You fiest need to remove the all non numeric caracters and teh 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