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

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

已邀请:

6 个回复

您必须弄清楚数据并将car 2拆分为varcharcar和int2。模式可以像WORD SPACE NUMBER一样简单，您可以使用PATINDEX或CHARINDEX与SUBSTRING一起基于SPACE将其拆分。然后您可以按两列排序。这是一个有效的例子

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)))

豆兢

输入数据不佳和不一致很难以编程方式修复。但是，您应该修复此数据，而不是在SELECT中，以便您的ORDER BY工作，但在数据中，所以您不必再担心这一点。您应该考虑为相关数据的“单词”和“数字”部分创建单独的列。然后，您可以运行一个脚本，尝试将数据放入正确的列，然后运行任何必要的手动后续操作。您将更改应用程序逻辑和可能的前端，以保持数据进入数据库有效。任何不足之处都只会导致数据的无效排序。

氮顺

编写一个重新格式化字符串的函数（例如，汽车10变为汽车010），然后用它来转换SQL查询中的列数据。您必须检查此解决方案的性能。或者......您可以按照上述相同的方式转换现有数据，以便在SQL中进行字母顺序排序，而无需在查询时应用任何函数。

娜拐赐巫潭

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的空格后的文本以便正确排序

舞备联

怎么样的：

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

谷靛

你可以利用这个事实

patindex('%...', _col_)

匹配最后一个字符和

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

匹配任意数量的字符。因此，只要数字位于列值的末尾，您就可以轻松地从列中提取数值（StackOverflow不会让我将单词UNION放在帖子中 - 用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

要回复问题请先登录或注册

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

6 个回复

发起人

问题状态

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

与内容相关的链接

6 个回复

发起人

问题状态