可以用函数处理~~
你参考下,需要新建下函数
CREATE FUNCTION dbo.NumberInStrSortIndex
(@INPUT VARCHAR(MAX))
RETURNS INT
AS
BEGIN
DECLARE @INTSTR VARCHAR(10)
SET @INTSTR=''
DECLARE @INDEX INT,@NEXT INT
SET @INDEX=PATINDEX('%[0-9]%',@INPUT)
WHILE @INDEX>0
BEGIN
SET @INTSTR=@INTSTR+SUBSTRING(@INPUT,@INDEX,1)
SET @INPUT=RIGHT(@INPUT,LEN(@INPUT)-@INDEX)
SET @INDEX=PATINDEX('%[0-9]%',@INPUT)
IF @INDEX>1
BREAK
END
IF @INTSTR=''
RETURN 2147483647
RETURN CAST(@INTSTR AS INT)
END
然后就可以排序了
SELECT * FROM TB ORDER BY dbo.NumberInStrSortIndex(room),room
你参考下,需要新建下函数
CREATE FUNCTION dbo.NumberInStrSortIndex
(@INPUT VARCHAR(MAX))
RETURNS INT
AS
BEGIN
DECLARE @INTSTR VARCHAR(10)
SET @INTSTR=''
DECLARE @INDEX INT,@NEXT INT
SET @INDEX=PATINDEX('%[0-9]%',@INPUT)
WHILE @INDEX>0
BEGIN
SET @INTSTR=@INTSTR+SUBSTRING(@INPUT,@INDEX,1)
SET @INPUT=RIGHT(@INPUT,LEN(@INPUT)-@INDEX)
SET @INDEX=PATINDEX('%[0-9]%',@INPUT)
IF @INDEX>1
BREAK
END
IF @INTSTR=''
RETURN 2147483647
RETURN CAST(@INTSTR AS INT)
END
然后就可以排序了
SELECT * FROM TB ORDER BY dbo.NumberInStrSortIndex(room),room
SELECT '12-13室' UNION ALL
SELECT '6室' UNION ALL
SELECT '抽血处' UNION ALL
SELECT '低频B超:14-19室' UNION ALL
SELECT '高频B超:8-9室' UNION ALL
SELECT '室号10'
)
,t1 AS (
SELECT room,
PATINDEX('%[0-9]%',room) numStart
FROM table1
)
,t2 AS (
SELECT *,
CASE WHEN numStart = 0 THEN
''
ELSE
PATINDEX('%[^0-9]%',STUFF(room,1,numStart-1,''))
END numEnd
FROM t1
)
,t3 AS (
SELECT *,
CASE WHEN numStart = 0 THEN
room
WHEN numEnd = 0 THEN
RIGHT('00000000000'+STUFF(room,1,numStart-1,''),11)
ELSE
RIGHT('00000000000'+SubString(room,numStart,numEnd-1),11)
END sort
FROM t2
)
SELECT * FROM t3 ORDER BY sort
room numStart numEnd sort
---------------- ----------- ----------- ----------------------
6室 1 2 00000000006
高频B超:8-9室 6 2 00000000008
室号10 3 0 00000000010
12-13室 1 3 00000000012
低频B超:14-19室 6 3 00000000014
抽血处 0 0 抽血处