Sort alphanumeric values in SQL Server

Databases
1

Current result:

2
2a
2aa
2ab
2b
2c
3
3a
3aa
3ab
3b
3c

Expected result:

2
2a
2b
2c
3
3a
3b
3c
2aa
2ab
3aa
3ab

This is what I tried:

Select LineNo FROM [Table] 
WHERE LineNo!='' 
ORDER BY CASE WHEN ISNUMERIC(LineNo)=1 
THEN CAST(LineNo as int) WHEN PATINDEX('%[^0-9]%',LineNo) > 1 
THEN CAST( LEFT( LineNo, PATINDEX('%[^0-9]%',LineNo) - 1) as int) 
ELSE 2147483648 END, 
CASE WHEN ISNUMERIC(LineNo)=1 
THEN NULL WHEN PATINDEX('%[^0-9]%',LineNo) > 1 
THEN SUBSTRING( LineNo, PATINDEX('%[^0-9]%',LineNo) , 50 ) 
ELSE LineNo END
2

three questions

1/ are those the actual values you want to sort, or did you just make up a bunch of 2aa and 3b stuff for illustrative purposes?

2/ i presume “this is what i tried” didn’t work the way you want – what did it produce, and why was it wrong?

3/ can you please explain in words, not in code, what your sort order depends on

3

1.These are my actual values I want to sort.
2.Yes , the above mentioned code which was I tried to sort values but it didn’t work 100%
this solution fails for two digit alphanumeric values occurs along with single digit alphanumeric values
i.e: 2,2a*,2aa,2ab*,2b,2c
2aa,2ab should come at last
3.The dbms is SQL Server 2017

4

can you please explain in words, not with examples, how your sort order should work

5

for example…

“sort all 1- and 2-byte LineNo values together first, followed by all values longer than 2 bytes after that, and within those two groups, sort by LineNo”

ORDER 
    BY CASE WHEN LEN(LineNo ) > 2
            THEN 'humpty'
            ELSE 'dumpty' END
      , LineNo
6

I cannot test it because I am sitting at the airport waiting for my flight but what about a very simple solution

ORDER BY LENGTH(colname), colname
7

What do you have going on that you need to do this?

8

no, that’s not it

OP wants to see this –

2
2a
2b
2c
3
3a

by sorting on LENGTH(colname) first, you get all 1-character codes first, so 3 would be up after 2 and ahead of 2a

9

Actually, that is not what he wants. look at his post again. His expected result is not a real sort which is why I asked what he needs that for.

10

how can you say that??? i copied directly from OP’s post which said “Expected result:”

12

My bad. Too many hours staring at the screen. :smiley:

13

You are right.

Let’s try this one

ORDER BY colname REGEXP '^[0-9]’, colname REGEXP ’^[^0-9]’
14

sorry, that gives “Incorrect syntax near ‘REGEXP’.”

you guys are welcome to test your solutions in sqlfiddle.com

here’s the schema setup –

create table t
( x  varchar(5) );

insert into t values
('2'),('2a'),('2aa'),('2ab'),('2b'),('2c'),
('3'),('3a'),('3aa'),('3ab'),('3b'),('3c');

my solution works exactly as desired, by the way

15

You solution works only for digits from 0-9. I guess there might be more entries with greater digits in the real scheme.

16

still waiting for @saravanacse1 to state the actual parameters of the problem in words

perhaps there are no entries with more than one digit

my solution works perfectly for the given data

17

This is how I implement the logic but it was not working
Data source/expected result

17A	Post-1986 depreciation adjustment
17B	Adjusted gain or loss
17C	Depletion (other than oil & gas)
17D	Oil, gas, & geothermal—gross income
17E	Oil, gas, & geothermal—deductions
17F	Other AMT items
18A	Tax-exempt interest income
18B	Other tax-exempt income
18C	Nondeductible expenses
20C	Fuel tax credit information
20D	Qualified rehabilitation expenditures (other than rental real estate)
20E	Basis of energy property
20F	Recapture of low-income housing credit (section 42(j)(5))
20G	Recapture of low-income housing credit (other)
20H	Recapture of investment credit
20I	Recapture of other credits
20J	Look-back interest—completed long-term contracts
20K	Look-back interest—income forecast method
20L	Dispositions of property with section 179 deductions
20M	Recapture of section 179 deduction
20N	Interest expense for corporate partners
20O	Section 453(l)(3) information
20P	Section 453A(c) information
20Q	Section 1260(b) information
20R	Interest allocable to production expenditures
20S	CCF nonqualified withdrawals
20T	Depletion information—oil and gas
20V	UBTI Ordinary
20V	UBTI Capital Gain/(Loss) & Sec. 1256
20W	Precontribution gain (loss)
20X	Reserved
20Y	Net investment income
20Z	Section 199A information
20AA	Section 704(c) information
20AB	Section 751 gain (loss)
20AC	Section 1(h)(5) gain (loss)
20AD	Deemed section 1250 unrecaptured gain
20AE	Excess taxable income
20AF	Excess business interest income
20AG	Gross receipts for section 59A(e)
20AH	Other information
20AH	Other information
20AH	Other information
20AH	Other information
20AH	Other information
20AH	Other information
20AH	Other information
20AH	Other information
20AH	Other information
20AH	Other information
M-1	Unrealized & Other Book-Tax Differences
M-1	Tax Exempt Income
M-1	Non-Ded Expense
M-1	U.S. Tax Withholding
M-2	Other Increase/(Decrease)

drop Table if exists #temp2
SELECT dbo.udf_GetNumber([K1LineNo]) number,K1LineName,[K1LineNo],
PATINDEX('%[^0-9]%',[K1LineNo]) num_index,
STUFF([K1LineNo], PATINDEX('%[^0-9]%',[K1LineNo]), 2, '' ) num_val,
SUBSTRING(  [K1LineNo], PATINDEX('%[^0-9]%',[K1LineNo]) , 50  ) string_val,
 OVER() AS TotalRows ,row_number() over (
Order By CASE
  WHEN ISNUMERIC([K1LineNo])=1
  THEN CAST([K1LineNo] as int)

  WHEN PATINDEX('%[^0-9]%',[K1LineNo]) > 1
  THEN CAST( LEFT( [K1LineNo], PATINDEX('%[^0-9]%',[K1LineNo]) - 1) as int)
  ELSE 2147483648
END,

CASE
  WHEN ISNUMERIC([K1LineNo])=1
  THEN NULL

  WHEN PATINDEX('%[^0-9]%',[K1LineNo]) > 1
  THEN SUBSTRING(  [K1LineNo], PATINDEX('%[^0-9]%',[K1LineNo]) , 50  )
  ELSE [K1LineNo]
END ,
CASE
WHEN LEN(SUBSTRING(  [K1LineNo], PATINDEX('%[^0-9]%',[K1LineNo]) , 50  ) )=2
THEN STUFF([K1LineNo], PATINDEX('%[^0-9]%',[K1LineNo]), 2, '' )
ELSE [K1LineNo]
END

)*100 as r_n

into #temp2



  FROM [Table] WHERE [K1LineNo]!=''
ORDER BY
CASE
  WHEN ISNUMERIC(K1LineNo)=1
  THEN CAST(K1LineNo as int)

  WHEN PATINDEX('%[^0-9]%',K1LineNo) > 1
  THEN CAST( LEFT( K1LineNo, PATINDEX('%[^0-9]%',K1LineNo) - 1) as int)
  ELSE 2147483648
END,

CASE
  WHEN ISNUMERIC(K1LineNo)=1
  THEN NULL

  WHEN PATINDEX('%[^0-9]%',K1LineNo) > 1
  THEN SUBSTRING(  K1LineNo, PATINDEX('%[^0-9]%',K1LineNo) , 50  )
  ELSE K1LineNo
END ,
CASE
WHEN LEN(SUBSTRING(  [K1LineNo], PATINDEX('%[^0-9]%',[K1LineNo]) , 50  ) )=2
THEN STUFF([K1LineNo], PATINDEX('%[^0-9]%',[K1LineNo]), 2, '' )
ELSE [K1LineNo]
END

SELECT * FROM  #temp2
18

are you sure now? you’re not going to change your mind again? because in your original post, there were no 2-digit numerical prefixes, only 1-digit

so… okay, try this –

ORDER 
    BY CASE WHEN ISNUMERIC(SUBSTRING(LineNo,1,1)) = 0
            THEN 'moe'
            WHEN LEN(LineNo) > 3
            THEN 'larry'
            ELSE 'curly' END
     , LineNo
19

The number prefix may be of any size it varies from 0,1,1a,1aa,1b,…20,20a,20b,20aa.

ORDER 
    BY CASE WHEN ISNUMERIC(SUBSTRING(LineNo,1,1)) = 0
            THEN 'moe'
            WHEN LEN(LineNo) > 3
            THEN 'larry'
            ELSE 'curly' END

This solution used only to check the condition , how to process inside THEN and WHEN block ?

20

so at most 2 digits, then? my solution covers this

i’m sorry, could you rephrase this please?

also, did you test it? does it work for you?

21 
CASE WHEN ISNUMERIC(SUBSTRING(LineNo,1,1)) = 0
            THEN '**moe**'
            WHEN LEN(LineNo) > 3
            THEN '**larry**'
            ELSE '**curly**' END

what should be used in the place of ‘moe’, ‘larry’,‘curly’ ?