SQL Sorting and hyphens

by

I learned something new, just like you as well

I believe the difference is between a “String Sort” vs a “Word Sort” (ignores hyphen)

Sample difference between WORD sort and STRING sort
http://andrusdevelopment.blogspot.com/2007/10/string-sort-vs-word-sort-in-net.html

From Microsoft
http://support.microsoft.com/kb/322112

For example, if you are using the SQL
collation
“SQL_Latin1_General_CP1_CI_AS”, the
non-Unicode string ‘a-c’ is less than
the string ‘ab’ because the hyphen
(“-“) is sorted as a separate
character that comes before “b”.
However, if you convert these strings
to Unicode and you perform the same
comparison, the Unicode string N’a-c’
is considered to be greater than N’ab’
because the Unicode sorting rules use
a “word sort” that ignores the hyphen.

I did some sample code
you can also play with the COLLATE to find the one to work with your sorting

DECLARE @test TABLE
(string VARCHAR(50))

INSERT INTO @test SELECT 'co-op'
INSERT INTO @test SELECT 'co op'
INSERT INTO @test SELECT 'co_op'

SELECT * FROM @test ORDER BY string --COLLATE SQL_Latin1_General_Cp1_CI_AS
--co op
--co-op
--co_op

SELECT * FROM @test ORDER BY CAST(string AS NVARCHAR(50)) --COLLATE SQL_Latin1_General_Cp1_CI_AS
--co op
--co_op
--co-op

More Related Contents:

  1. Is it worth using parallel OpenMp sort with an array of 4000 elements? [closed]
  2. Natural (human alpha-numeric) sort in Microsoft SQL 2005
  3. How to use Thrust to sort the rows of a matrix?
  4. what is natural ordering when we talk about sorting?
  5. How do I sort one vector based on values of another
  6. Why is the minimalist, example Haskell quicksort not a “true” quicksort?
  7. What is the purpose of shuffling and sorting phase in the reducer in Map Reduce Programming?
  8. What is a better way to sort by a 5 star rating?
  9. Associatively sorting a table by value in Lua
  10. Code an Excel VBA sort with a custom order and a value containing commas
  11. SQL Server GUID sort algorithm. Why?
  12. is it possible to do quicksort of a list with only one passing?
  13. jqGrid with Custom sorttype
  14. Radix Sort for Negative Integers
  15. Why does Rust not implement total ordering via the Ord trait for f64 and f32?
  16. How do I sort an array of structs by multiple values?
  17. What is the shortest way to simply sort an array of structs by (arbitrary) field names?
  18. pandas groupby sort descending order
  19. Sort a Table[] in Lua
  20. Underscore.js Case Insensitive Sorting
  21. OpenGL depth sorting
  22. How to define (and name) the corresponding safe term comparison predicates in ISO Prolog?
  23. In selection sort, if we have two duplicate elements, what is the behavior of the algorithm?
  24. How to sort Date which is in string format in java?
  25. How to insert multiple records and get the identity value?
  26. How can a LEFT OUTER JOIN return more records than exist in the left table?
  27. Aggregate bitwise-OR in a subquery
  28. “Prevent saving changes that require the table to be re-created” negative effects
  29. What arguments does Python sort() function have?
  30. Excel VBA Quickest way to sort an array of numbers in descending order?

Leave a Comment