Sorting a column having special charaters in SQL Server 2008

I have a table with column Name NVARCHAR(100).

The data in the table looks like this:

Wang
Bergeron
Z开发者_JAVA技巧hang
Sorrentino
Mazumdar
Boggs
Fricker
Barkwell
Atkinson
Mah
Test CB1 C1*(
--Test Contact--
--TEST CONTACT--
--ABC CONTACT--
Retest Contact
Janzen
Seto
Boggs
Fricker
Barkwell

and I am expecting the results to be like

--ABC CONTACT--
--Test Contact--
--TEST CONTACT--
Atkinson
Barkwell
Barkwell
Bergeron
Boggs
Boggs
Fricker
Fricker
Janzen
Mah
Mazumdar
Retest Contact
Seto
Sorrentino
Test CB1 C1*(
Wang
Zhang

Request your kind support to re-solve this sorting

You could use a Case-Statement in your Order By.

declare  @temp table(
    Name varchar(100)
)
insert into @temp 
    SELECT * FROM (SELECT 'Wang' UNION ALL SELECT 'Bergeron' UNION ALL SELECT 'Zhang' UNION ALL SELECT 'Sorrentino' UNION ALL SELECT 'Mazumdar' UNION ALL SELECT 'Boggs' UNION ALL SELECT 'Fricker' UNION ALL SELECT 'Barkwell' UNION ALL SELECT 'Atkinson' UNION ALL SELECT 'Mah' UNION ALL SELECT 'Test CB1 C1*(' UNION ALL SELECT '--Test Contact--' UNION ALL SELECT '--TEST CONTACT--' UNION ALL SELECT '--ABC CONTACT--' UNION ALL SELECT 'Retest Contact' UNION ALL SELECT 'Janzen' UNION ALL SELECT 'Seto' UNION ALL SELECT 'Boggs' UNION ALL SELECT 'Fricker' UNION ALL SELECT 'Barkwell')AS T(Spalte);

select * from @temp
    ORDER BY (CASE WHEN [Name] LIKE '-%' THEN 0 ELSE 1 END), [Name]

Maybe with a case statement in the order clause :

SELECT *
FROM myTable
ORDER BY CASE WHEN Name LIKE '-%' THEN 1 ELSE 0 END DESC,
         Name ASC