sql-server-2005 - SQL排序和连字符

Question

有没有一种方法可以轻松地在SQL Server 2005中进行排序，而忽略字符串字段中的连字符？当前，我必须执行REPLACE(fieldname，'-'，'')或函数以删除sort子句中的连字符。我希望可以在存储过程或其他内容的顶部设置一个标志。

Access和GridView的默认排序似乎忽略了字符串中的连字符。

Answer 1

我也学到了一些新的东西

我相信“字符串排序”与“单词排序”之间的区别(忽略连字符)

WORD排序和STRING排序之间的样本差异
http://andrusdevelopment.blogspot.com/2007/10/string-sort-vs-word-sort-in-net.html

来自微软
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.

我做了一些示例代码
您还可以与COLLATE一起玩，以找到适合您的排序的

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