Need Help to sort alpha numeric values

• ritchakshah

  Grasshopper

  Points: 19

  More actions

  May 14, 2015 at 12:35 pm

  #296969

  Hello everyone,

  I need help to sort a column having alpha numberic values

  000000000000001

  000000000000005

  000000000000006

  00000000000002N

  00000000000002S

  0002

  12-I395

  14-I395

  9-I395

  AL001

  AL002

  MD0001

  MD0002

  MD0003

  VA0001

  VA0002

  Please advice , how do I do it?

  Thanks

  Kapil

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  May 14, 2015 at 12:39 pm

  #1797981

  ORDER BY <column name>

  Add DESC at the end if you want to sort in descending order

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Lowell

  SSC Guru

  Points: 323504

  More actions

  May 14, 2015 at 12:41 pm

  #1797982

  they are already sortable varchar values, so how do you want to sort them differently than the natural order by text via ORDER BY [ColumnName]?

  the order you pasted them is already sorted.

  ;WITH MyCTE([ColumnName])

  AS

  (

  SELECT '000000000000001' UNION ALL

  SELECT '000000000000005' UNION ALL

  SELECT '000000000000006' UNION ALL

  SELECT '00000000000002N' UNION ALL

  SELECT '00000000000002S' UNION ALL

  SELECT '0002' UNION ALL

  SELECT '12-I395' UNION ALL

  SELECT '14-I395' UNION ALL

  SELECT '9-I395' UNION ALL

  SELECT 'AL001' UNION ALL

  SELECT 'AL002' UNION ALL

  SELECT 'MD0001' UNION ALL

  SELECT 'MD0002' UNION ALL

  SELECT 'MD0003' UNION ALL

  SELECT 'VA0001' UNION ALL

  SELECT 'VA0002'

  )

  SELECT * FROM MyCTE ORDER BY [ColumnName]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ritchakshah

  Grasshopper

  Points: 19

  More actions

  May 14, 2015 at 12:56 pm

  #1797989

  Thanks , It works

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  May 14, 2015 at 1:39 pm

  #1798002

  Just to pile on a bit, use the desired collation to get the desired sort order

  😎

  Collation example with Lowell's data sample:

  use tempdb;

  go

  ;WITH MyCTE([ColumnName])

  AS

  (

  SELECT N'000000000000001' UNION ALL

  SELECT N'000000000000005' UNION ALL

  SELECT N'000000000000006' UNION ALL

  SELECT N'00000000000002N' UNION ALL

  SELECT N'00000000000002S' UNION ALL

  SELECT N'0002' UNION ALL

  SELECT N'12-I395' UNION ALL

  SELECT N'14-I395' UNION ALL

  SELECT N'9-I395' UNION ALL

  SELECT N'AL001' UNION ALL

  SELECT N'AL002' UNION ALL

  SELECT N'MD0001' UNION ALL

  SELECT N'MD0002' UNION ALL

  SELECT N'MD0003' UNION ALL

  SELECT N'VA0001' UNION ALL

  SELECT N'VA0002'

  )

  SELECT [ColumnName] COLLATE Arabic_100_BIN FROM MyCTE ORDER BY [ColumnName];