-2

I want to order alphanumeric value within a cell in MS SQL server.

Example -

There is a value in a column - CBA321, and my desired output is ABC123.

If anyone knows how can I sort it within a cell, it will help. Thank you!

Order By is not working here. I thing UserDefinedFunction or Scalar Function is one solution, but I am a beginner, so not knowing much about it.

Shubham Jha
  • 3
  • 3
  • `ORDER BY` orders rows, not characters within a string. What, in your logic, defines that `'1'` is "greater than" `'Z'` as well? What about something like `'%'`? What defines the order of the characters. – Thom A Jan 10 '23 at 14:08
  • 1
    This looks like the job of your application layer, the database extracts the required data but your application should be where you implement such a process, or are you validating and storing input incorrectly? – Stu Jan 10 '23 at 14:12
  • The required solution related to application layer not DB layer (as mentioned in a comment). People don't do think like this in databases. But you can find useful tipps here for sorting algorithm: https://stackoverflow.com/questions/6972635/efficient-string-sorting-algorithm – HowToTellAChild Jan 10 '23 at 21:21

1 Answers1

1

For Azure SQL Database or SQL Server 2022 you can use string_agg over generate_series for this, eg

declare @v varchar(200) = 'CBA321'

select string_agg( substring(@v,s.value,1), '') within group (order by  substring(@v,s.value,1))
from  generate_series(1,len(@v)) s

which outputs

123ABC

because numerals come before alpha characters in my collation. If you want the alphas first, something like

declare @v varchar(200) = 'CBA321'

select string_agg( substring(@v,s.value,1), '') within group (order by  case when substring(@v,s.value,1) < 'A' then 1 else 0 end, substring(@v,s.value,1) )
from  generate_series(1,len(@v)) s
GuidoG
  • 11,359
  • 6
  • 44
  • 79
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 2
    I don't think there's *any* collations where numerals come *after* A-z, if I'm honest. – Thom A Jan 10 '23 at 14:20
  • The answerer is David here, @ShubhamJha . – Thom A Jan 11 '23 at 18:23
  • @David Thank you so much! It helps. Just wanted to check how can use a column (eg. Invoice) in a table (eg. Sales Report) to use this formula. – Shubham Jha Jan 12 '23 at 05:22
  • @David, can you pls help me to use this function for a column (Invoice) in Sales Table. I have 4million of records, so I hope it won't slow down – Shubham Jha Feb 07 '23 at 08:36