SQL collation and hyphens

Question

Is there a way to easily sort in SQL Server 2005 by ignoring hyphens in the string field? Currently, I have to do REPLACE (field name, '-', '') or a function to remove the hyphen in the sort clause. I was hoping there was a flag that I could set at the top of the stored procedure or something like that.

Default Access and Sorting GridView seems to ignore hypen in rows.

Answer 1

I learned something new, just like you

I find the difference between " Sort String " and " Word Sort " (ignores hyphen)

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

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

, SQL "SQL_Latin1_General_CP1_CI_AS", -Unicode 'a-c' , 'ab', ( "-" ) , "b". , , , Unicode N'a-c ' , N'ab ' " ", .

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