SQL 如何拆分字符串中的数值后排序
通常情形下,一列数据都是整体参与排序,但如果当字段是由字母跟序号组合在一起的复合列,比如现有表TData中列名为NameSerial的一组数据:
Mike105
Rose11
Rose1
Rose5
Mike6
现在需要先按名字排序,再按序号排序。比如Mike6要排在Mike105前面,期望的结果如下:
Mike6
Mike105
Rose1
Rose5
Rose11
由于SQL不支持对字段的单词拆分,所以只能写存储过程拆分数据,然后使用SQL查询来排序。以SQL Server2012为例,自定义数据拆分标量值函数dbo.words如下:
CREATE FUNCTION dbo.words(@word VARCHAR(100) , @part INT) RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @sub VARCHAR(100)
DECLARE @tmp VARCHAR(10)
DECLARE @numIndex INT
DECLARE @len INT
DECLARE @i INT
SET @i = 1
SET @len = Len(@word)
WHILE @i<=@len
BEGIN
SET @tmp = Substring(@word,@i,1)
IF(ISNUMERIC(@tmp)=1)
BEGIN
SET @numIndex = @i
BREAK
END
SET @i=@i+1
END
IF(@part=1)
BEGIN
SET @sub=Substring(@word,1,@numIndex-1)
END
ELSE
BEGIN
SET @sub=Substring(@word,@numIndex,@len-@numIndex+1)
END
RETURN @sub
END
其中参数@part用于指示当前要拆分的部分,1为名字部分,2为序号部分。
需要注意的是,数值部分拆分出来后,要转换为整数后再排序,否则仍然跟整体排序没区别。之后的排序,只需要如下SQL即可:
SELECT * FROM TData ORDER BY dbo.words(NameSerial,1),cast(dbo.words(NameSerial,2) as int)
本例虽然通过自定义数据库函数的方式实现了拆分函数,但并不通用,且不能移植到其他数据库。
这种情况,如果有集算器就方便多了,用SPL实现只要一句话:
connect("db").query("SELECT * FROM TData").sort(NameSerial.words@w())
集算器SPL是解决SQL难题的专业脚本语言,它语法简单,符合自然思维,天然分步、层次清晰的面向过程计算语言。它采用与数据库无关的统一语法,编写的算法可在数据库间无缝迁移。它是桌面级计算工具,即装即用,配置简单,调试功能完善,可设置断点、单步执行,每步执行结果都可查看。请参阅SQL 解题手
SPL也能很方便地嵌入到JAVA应用,可参考:Java 如何调用 SPL 脚本
具体使用方法可参考: 如何使用集算器