Sort VARCHAR column with alphanumeric inputs

Question

I am using SQL Server column VARCHAR(50) and I want to sort it as follows:

 1A 1B 2 2 3 4A 4B 4C 5A 5B 5C 5N 14 Draft 21 22A 22B 23A 23B 23C 23D 23E 25 26 FR01584 MISC 

What I still have:

 Select * From viewASD ORDER BY Case When IsNumeric(LEFT(asdNumNew,1)) = 1 Then CASE When IsNumeric(asdNumNew) = 1 Then Right(Replicate('0',20) + asdNumNew + '0', 20) Else Right(Replicate('0',20) + asdNumNew, 20) END When IsNumeric(LEFT(asdNumNew,1)) = 0 Then Left(asdNumNew + Replicate('',21), 20) End 

But this SQL statement puts “14 Draft” right after “26”.

Can anyone help? Thanks

Answer 1

Your WHERE statement ... is strangely complex.

It looks like you want to sort by leading numeric digits in whole orders, and then sort by remainder. If so, you should do it as separate sentences, and not try to do it all in one. The specific problem you are facing is that you only allow a single digit number instead of two or more. (And there is no such thing as two .)

Here's your fix as well as SQLFiddle using two separate calculated column tests for your ORDER BY. (Note that this assumes that the numerical part of asdNumNew will match the T-SQL int . If not, you need to configure CAST and the maximum value on the first ELSE.)

 SELECT * FROM viewASD ORDER BY CASE WHEN ISNUMERIC(asdNumNew)=1 THEN CAST(asdNumNew as int) WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1 THEN CAST( LEFT( asdNumNew, PATINDEX('%[^0-9]%',asdNumNew) - 1 ) as int) ELSE 2147483648 END, CASE WHEN ISNUMERIC(asdNumNew)=1 THEN NULL WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1 THEN SUBSTRING( asdNumNew, PATINDEX('%[^0-9]%',asdNumNew) , 50 ) ELSE asdNumNew END 

Answer 2

If all the numbers in a line are small enough, say no more than 10 digits, you can expand all the numbers in a line into exactly 10 digits:

123A → 0000000123A

  S4 -> S0000000004 

A3B89 → A0000000003B0000000089

etc. and then sort them

 -- Expand all numbers within S by zeros to be MaxLen create function [dbo].ExpandNumbers(@S VarChar(4000), @maxlen integer) returns VarChar(4000) as begin declare @result VarChar(4000); declare @buffer VarChar(4000); declare @Ch Char; declare @i integer; set @buffer = ''; set @result = ''; set @i = 1; while (@i <= len(@S)) begin set @Ch = substring(@S, @i, 1); if ((@Ch >= '0') and (@Ch <= '9')) set @buffer = @buffer + @Ch else begin if (len(@buffer) > 0) set @result = @result + right(replicate('0', @maxlen) + @buffer, @maxlen); set @buffer = ''; set @result = @result + @Ch; end; set @i = @i + 1; end; if (len(@buffer) > 0) set @result = @result + right(replicate('0', @maxlen) + @buffer, @maxlen); return @result; end; -- Final query is select * from viewASD order by [dbo].ExpandNumbers(asdNumNew) 

Answer 3

TRY IT
declare @t table (Number nvarchar (20)) insert into @t Select 'L010' union all select 'L011' union all select 'L011' union all select 'L001' union all select 'L012' union all select '18'
union all select '8' union all select '17'

  union all select 'B004' union all SELECT 'B006' union all SELECT 'B008' union all SELECT 'B018' union all SELECT 'UG001' union all SELECT 'UG011' union all SELECT 'G001' union all SELECT 'G002' union all SELECT 'G011' select * from @t order by cast(SUBSTRING(Number, 1, case when patindex('%[^0-9]%',Number) > 0 then patindex('%[^0-9]%',Number) - 1 else LEN(Number) end) as int), Number 

o / r

 **Number** B004 B006 B008 B018 G001 G002 G011 L001 L010 L011 L011 L012 UG001 UG011 8 17 18 

Answer 4

I had something similar, but with the ability to dash both leading characters and trailing spaces. This code worked for me.

 SELECT my_column, PATINDEX('%[^0-9]%',my_column) AS first_alpha_position, CONVERT(INT, CASE WHEN PATINDEX('%[^0-9]%',my_column) = 0 OR PATINDEX('-%',my_column) = 1 THEN ABS(my_column) ELSE SUBSTRING(my_column,1,PATINDEX('%[^0-9]%',my_column) -1) END) AS numeric_value, LTRIM( SUBSTRING(my_column,PATINDEX('%[^0-9]%',my_column),LEN(my_column)-PATINDEX('%[^0-9]%',my_column)+1) ) AS alpha_chars FROM my_table ORDER BY numeric_value,alpha_chars 

Answer 5

create an Employee table (id int, Registration_no varchar (50), name varchar (50))

insert into employee values ​​(1, DLW / TTC / 19/3 ′, RAMESH) insert into employee values ​​(2, DLW / TTC / 19/2 ′, RAJEEV) insert into employee values ​​(3, DLW / TTC / 19/1 ′, RUPAK) insert in employee values ​​(4, DLW / TTC / 19/4 ′, RAMLAAL) insert in employee values ​​(5, DLW / TTC / 19/8 ′, RITESH) insert in employee values ​​(6, DLW / TTC / 19/6 ′, HRITIK) ​​insert in the values ​​of Employee (7, DLW / TTC / 19/9 ′, ROSHAN) insert in the values ​​of Employee (8, DLW / TTC / 19/7 ′, RUPALI) insert in the values ​​of Employee (9 , DLW / TTC / 19/5 ′, SHRISTI) insert into the values ​​of the Employee (10, DLW / TTC / 19/10 ′, ROSHNI)

select * from employees

Hi guys, I have the table above, please help me solve this problem

I want to order its column (Registration_no) one by one