SQL排序和连字符 [英] SQL Sorting and hyphens
问题描述
有没有一种方法可以在SQL Server 2005中轻松排序,而忽略字符串字段中的连字符?目前,我必须执行REPLACE(fieldname,'-','')或函数以删除sort子句中的连字符.我希望可以在存储过程或其他内容的顶部设置一个标志.
Is there a way to easily sort in SQL Server 2005 while ignoring hyphens in a string field? Currently I have to do a REPLACE(fieldname,'-','') or a function to remove the hyphen in the sort clause. I was hoping there was a flag I could set at the top of the stored procedure or something.
访问和GridView默认排序似乎忽略了字符串中的连字符.
Access and the GridView default sorting seems to ignore the hypen in strings.
推荐答案
我也和您一样学到了新的东西
I learned something new, just like you as well
我相信"字符串排序"与"单词排序"(忽略连字符)之间的区别
I believe the difference is between a "String Sort" vs a "Word Sort" (ignores hyphen)
WORD排序和STRING排序之间的样本差异 http://andrusdevelopment.blogspot.com/2007/10/string-sort-vs-word-sort-in-net.html
Sample difference between WORD sort and STRING sort http://andrusdevelopment.blogspot.com/2007/10/string-sort-vs-word-sort-in-net.html
来自Microsoft http://support.microsoft.com/kb/322112
From Microsoft http://support.microsoft.com/kb/322112
例如,如果您使用的是SQL 校对 "SQL_Latin1_General_CP1_CI_AS", 非Unicode字符串'a-c'小于 字符串"ab",因为连字符 (-")单独排序 "b"之前的字符. 但是,如果您转换这些字符串 到Unicode,您执行相同的操作 比较,Unicode字符串N'a-c' 被认为大于N'ab' 因为Unicode排序规则使用 忽略连字符的单词排序".
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一起玩,找到适合您的排序的
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
这篇关于SQL排序和连字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!