# sql排序 数字+汉字 汉字永远在最后

0
[待解决问题]

0

http://www.cnblogs.com/shuai/archive/2010/05/25/1743278.html

http://www.aspxhome.com/database/mssql/20104/1373723.htm

LanJerry | 园豆：168 (初学一级) | 2015-03-04 10:37

0

0

0

```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
RETURN @match
END
GO

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

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

**********************/```

0

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```
.悟 | 园豆：213 (菜鸟二级) | 2015-06-04 22:11
0

Merger | 园豆：225 (菜鸟二级) | 2017-04-12 13:54

您需要登录以后才能回答，未注册用户请先注册