SQL 排序和连字符 [英] SQL Sorting and hyphens

问题描述

是否有一种方法可以在忽略字符串字段中的连字符的同时轻松地在 SQL Server 2005 中进行排序?目前我必须做一个 REPLACE(fieldname,'-','') 或一个函数来删除排序子句中的连字符.我希望可以在存储过程的顶部或其他地方设置一个标志.

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.

Access 和 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

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