如何对SQL Server中包含单词和数字的VARCHAR列进行排序？

Question

我有一个包含字母和数字的varchar（100）字段。 这些值通常以汽车1，汽车10，汽车100，汽车20的形式出现。但是这些值可以包含数字前的任何单词。有什么方法可以将这些数值进行数值排序，以便汽车2会在汽车10之前出现？谢谢。

Answer 1

编写一个重新格式化字符串的函数（例如car 10变成car 010），然后用它转换SQL查询中的列数据。您必须检查此解决方案的性能。

或者...您可以按照上述相同的方式转换现有数据，以便在查询时不需要应用任何函数就可以在SQL中按字母排序。

Answer 2

您必须计算出您的数据并将car 2分成varchar car和int 2。

模式可以像WORD SPACE NUMBER简单，你可以根据使用PATINDEX或CHARINDEXSPACE连同SUBSTRING上拆呢。

然后您可以按两列进行排序。

这里是一个工作示例

SET NOCOUNT ON 

Declare @Table table 
(
    Id INT Identity (1, 1), 
    StringValue VarChar (30) 
) 

INSERT INTO @Table (StringValue) VALUES ('CAR 10') 
INSERT INTO @Table (StringValue) VALUES ('CAR 20') 
INSERT INTO @Table (StringValue) VALUES ('CAR 2') 
INSERT INTO @Table (StringValue) VALUES ('CAR 3') 
INSERT INTO @Table (StringValue) VALUES ('CAR 4') 

INSERT INTO @Table (StringValue) VALUES ('SHIP 32') 
INSERT INTO @Table (StringValue) VALUES ('SHIP 310') 
INSERT INTO @Table (StringValue) VALUES ('SHIP 320') 
INSERT INTO @Table (StringValue) VALUES ('SHIP 33') 
INSERT INTO @Table (StringValue) VALUES ('SHIP 34') 


SELECT Id, 
    SubString (StringValue, 1, CharIndex (' ', StringValue)) ObjectName, 
    CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue))) ObjectId 
FROM @Table 
ORDER BY 2, 3 

SELECT Id, StringValue 
FROM @Table 
ORDER BY 
    SubString (StringValue, 1, CharIndex (' ', StringValue)), 
    CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue))) 

Answer 3

WITH TABLE_NAME(NAME) AS 
(
SELECT 'car 20' 
UNION ALL 
SELECT 'car 2' 
UNION ALL 
SELECT 'car 10' 
) 
SELECT 
    * 
FROM TABLE_NAME 
ORDER BY 
SUBSTRING(NAME,1,CHARINDEX(' ',NAME,1)),CAST(SUBSTRING(NAME,CHARINDEX(' ',NAME,1)+1,100) AS INT) 

SUBSTRING（NAME，1，CHARINDEX（ ' 'NAME，1）） - 前空间 CAST（SUBSTRING（NAME文本，CHARINDEX（'' ，NAME，1）+1,100）AS INT） - 将空格之后的文本转换为int，以便进行正确的排序

Answer 4

输入较差且不一致的数据很难以编程方式修复。但是，您应该修复此数据，而不是在您的SELECT中，以便您的ORDER BY可以工作，但是在数据中，因此您不必再担心这一点。

您应该考虑为相关数据的“单词”和“数字”部分创建单独的列。然后，您可以运行脚本来尝试将数据放入适当的列，然后执行任何必要的手动跟踪。你将会改变应用程序逻辑，甚至可能是前端来保持数据进入数据库有效。

任何不足都会导致数据无效排序。

Answer 5

如何像：

SELECT 
    col1, col1_ltrim 
, col1_sort 
    = CONVERT(INT 
     , SUBSTRING(col1_ltrim,1 
      , ISNULL(
       NULLIF(PATINDEX('%[^0-9]%',col1_ltrim),0)-1 
      , LEN(col1_ltrim) 
      ) 
     ) 
     ) 
FROM ( 
    SELECT col1 
    , col1_ltrim = STUFF(col1,1,PATINDEX('%[0-9]%',col1)-1,'') 
    FROM (
    SELECT 'car 505' UNION ALL 
    SELECT 'car 95' UNION ALL 
    SELECT 'car 8776 blue' UNION ALL 
    SELECT 'car' 
    ) a (col1) 
) a 
ORDER BY col1_sort 

你可以把它包在一个UDF的理智：

CREATE FUNCTION dbo.StringToInt (@string VARCHAR(128)) 
RETURNS INT AS 
BEGIN 

    SELECT @string = STUFF(@string,1,PATINDEX('%[0-9]%',@string)-1,'') 
    RETURN CONVERT(INT 
      , SUBSTRING(@string,1 
      , ISNULL(
       NULLIF(PATINDEX('%[^0-9]%',@string),0)-1 
       , LEN(@string) 
      ) 
      ) 
     ) 

END 

GO 

SELECT col1, col1_sort = dbo.StringToInt(col1) 
FROM (
    SELECT 'car 505' UNION ALL 
    SELECT 'car 95' UNION ALL 
    SELECT 'car 8776 blue' UNION ALL 
    SELECT 'car' 
) a (col1) 
ORDER BY col1_sort 

Answer 6

你可以采取的事实，即

patindex('%...', _col_) 

匹配最后一个字符和

patindex('%...%', _col_) 

匹配任意数量的字符。因此，只要这些数字位于列值的末尾，就可以很容易地从列中提取数值（*不会让我将UNION字放入后置替换UUU中）：

select Rec, 
case 
    when patindex('%[0-9]', Rec) > 0 
    then left(Rec, patindex('%[0-9]%', Rec)-1) 
    else Rec 
end, 
case 
    when patindex('%[0-9]', Rec) > 0 
    then cast(right(Rec, len(Rec)-patindex('%[0-9]%', Rec)+1) as int) 
end 
from (select 'SHIP34' Rec UUU select 'SHIP 33' UUU select 'SHIP 320' UUU select 'SHIP310' UUU select 'SHIP32' UUU select 'CAR 4X' UUU select 'CAR 4' UUU select 'CAR3' UUU select 'CAR 2' UUU select 'CAR20' UUU select 'CAR 10') TestData 
order by 
    case 
     when patindex('%[0-9]', Rec) > 0 
     then left(Rec, patindex('%[0-9]%', Rec)-1) 
     else Rec 
    end, 
    case 
     when patindex('%[0-9]', Rec) > 0 
     then cast(right(Rec, len(Rec)-patindex('%[0-9]%', Rec)+1) as int) 
    end