IF NOT OBJECT_ID('[ChineseSort]') IS NULL
    DROP TABLE ChineseSort
GO
CREATE TABLE ChineseSort(
[TID] INT IDENTITY(1,1) PRIMARY KEY,
[TITLE] NVARCHAR(100))
GO

--truncate table ChineseSort;
INSERT ChineseSort
SELECT '500K天上人间' UNION ALL
SELECT '鸟巢' UNION ALL
SELECT '五棵松' UNION ALL
SELECT '200K龙宫'  
GO

SELECT
    *
FROM ChineseSort
ORDER BY TITLE DESC;
/*
3    五棵松
2    鸟巢
1    500K天上人间
4    200K龙宫
*/

/**** 创建一个函数 *************/

IF OBJECT_ID (N'dbo.RegexMatch') IS NOT NULL
   DROP FUNCTION dbo.RegexMatch
GO
CREATE FUNCTION dbo.RegexMatch
    (
      @pattern VARCHAR(2000),
      @matchstring VARCHAR(8000)--Varchar(8000) got SQL Server 2000
    )
RETURNS INT
/* The RegexMatch returns True or False, indicating if the regular expression matches (part of) the string. (It returns null if there is an error).
When using this for validating user input, you'll normally want to check if the entire string matches the regular expression. To do so, put a caret at the start of the regex, and a dollar at the end, to anchor the regex at the start and end of the subject string.
*/ 
AS BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(255),
        @hr INT,
        @match BIT

    SELECT  @strErrorMessage = 'creating a regex object'
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
        --Specifying a case-insensitive match 
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
        --Doing a Test' 
    IF @hr = 0 
        EXEC @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
    IF @hr <> 0 
        BEGIN
            RETURN NULL
        END
    EXEC sp_OADestroy @objRegexExp
    RETURN @match
   END
GO

/**********查询   ********/

SELECT
    title
    FROM dbo.ChineseSort
ORDER BY dbo.RegexMatch('[u4e00-u9fa5]', title) DESC,title DESC;
/****************************
500K天上人间
200K龙宫
五棵松
鸟巢
**********************/

CREATE TABLE ChineseSort( [TID] INT IDENTITY(1,1) PRIMARY KEY, [TITLE] NVARCHAR(100)) GO

--truncate table ChineseSort;

INSERT ChineseSort SELECT '500K天上人间' UNION ALL SELECT '鸟巢' UNION ALL SELECT '五棵松' UNION ALL SELECT '200K龙宫' 

;
with tmp as
(
    select *,case when ascii(TITLE) < 128 then 1 else 0 end orderby from ChineseSort
)
select * from tmp
order by orderby desc,TITLE desc