使用字母数字输入对VARCHAR列进行排序

Question

使用字母数字输入对VARCHAR列进行排序

6

我正在使用SQL Server，该列为VARCHAR(50)，我想按以下方式对其进行排序：

我现在所拥有的是:

Select *
From viewASD
ORDER BY 
    Case When IsNumeric(LEFT(asdNumNew,1)) = 1 
         Then CASE When IsNumeric(asdNumNew) = 1 
                   Then Right(Replicate('0',20) + asdNumNew + '0', 20)
                   Else Right(Replicate('0',20) + asdNumNew, 20) 
              END
         When IsNumeric(LEFT(asdNumNew,1)) = 0 
         Then Left(asdNumNew + Replicate('',21), 20)
    End

但是这个SQL语句将“14 Draft”放在“26”后面。有人可以帮忙吗？谢谢。

- terezzy

5个回答

0

我的做法是将数字和字母部分分开，然后按照字母部分排序，再按照数字部分排序。

CREATE FUNCTION [admin].[GetUnitNumberAsIntFunc](@UnitNumber varchar(20))
RETURNS int
BEGIN
    DECLARE @intPosition int 
    SET @intPosition = PATINDEX('%[^0-9]%', @UnitNumber)

    WHILE @intNumber > 0
    BEGIN 
        SET @UnitNumber = STUFF(@UnitNumber, @intNumber, 1, '')
        SET @intPosition = PATINDEX('%[^0-9]%', @UnitNumber)
    END 

    RETURN ISNULL(@UnitNumber,9999) 
    
END;

CREATE FUNCTION [admin].[GetUnitNumberAsStrFunc](@UnitNumber varchar(20))
RETURNS varchar(20)
BEGIN
    DECLARE @intPosition int 
    SET @intPosition = PATINDEX('%[0-9]%', @UnitNumber)

    SET @UnitNumber = STUFF(@UnitNumber, @intPosition, 6, '')

    RETURN ISNULL(@UnitNumber,9999) 
    
END;

- ascendedcrow

0

如果字符串中的所有数字都相对较小，比如不超过10位数，那么你可以将字符串中的所有数字扩展为恰好10位数：

123A -> 0000000123A

 S4 -> S0000000004

A3B89 -> A0000000003B0000000089

等等，然后对它们进行排序。

-- Expand all numbers within S by zeros to be MaxLen
create function [dbo].ExpandNumbers(@S VarChar(4000), @maxlen integer) returns VarChar(4000)
as
begin
  declare @result VarChar(4000);
  declare @buffer VarChar(4000);
  declare @Ch Char;

  declare @i integer;

  set @buffer = '';
  set @result = '';
  set @i = 1;

  while (@i <= len(@S))
    begin
      set @Ch = substring(@S, @i, 1);


      if ((@Ch >= '0') and (@Ch <= '9')) 
        set @buffer = @buffer + @Ch
      else 
        begin
          if (len(@buffer) > 0) 
            set @result = @result + right(replicate('0', @maxlen) + @buffer, @maxlen);

          set @buffer = '';  
          set @result = @result + @Ch;
        end;

      set @i = @i + 1;  
    end;

  if (len(@buffer) > 0) 
    set @result = @result + right(replicate('0', @maxlen) + @buffer, @maxlen);

  return @result;
end;

-- Final query is

   select *
    from viewASD
order by [dbo].ExpandNumbers(asdNumNew)

- Dmitry Bychenko

0

我之前遇到过类似的问题，不仅可能有破折号作为开头字符，还可能有尾部空格。这段代码对我很有效。

SELECT 
    my_column,
    PATINDEX('%[^0-9]%',my_column) AS first_alpha_position,
    CONVERT(INT,
    CASE 
        WHEN PATINDEX('%[^0-9]%',my_column) = 0 OR PATINDEX('-%',my_column) = 1
            THEN ABS(my_column)
        ELSE SUBSTRING(my_column,1,PATINDEX('%[^0-9]%',my_column) -1)
    END) AS numeric_value,
    LTRIM(
        SUBSTRING(my_column,PATINDEX('%[^0-9]%',my_column),LEN(my_column)-PATINDEX('%[^0-9]%',my_column)+1)
) AS alpha_chars
FROM my_table
ORDER BY numeric_value,alpha_chars

- Robert Sievers

0

试一试

DECLARE @t table (Number nvarchar(20)) 
INSERT INTO @t 
SELECT           'L010' 
UNION ALL SELECT 'L011' 
UNION ALL SELECT 'L011' 
UNION ALL SELECT 'L001' 
UNION ALL SELECT 'L012' 
UNION ALL SELECT '18'  
UNION ALL SELECT '8' 
UNION ALL SELECT '17' 
UNION ALL SELECT 'B004'    
UNION ALL SELECT 'B006'    
UNION ALL SELECT 'B008'
UNION ALL SELECT 'B018'   
UNION ALL SELECT 'UG001'
UNION ALL SELECT 'UG011'   
UNION ALL SELECT 'G001'    
UNION ALL SELECT  'G002' 
UNION ALL SELECT 'G011';
    
SELECT Number 
FROM @t 
ORDER BY 
CAST
(
    SUBSTRING
    (
        Number
        , 1
        , CASE 
            WHEN patindex('%[^0-9]%',Number) > 0 THEN patindex('%[^0-9]%',Number) - 1 
            ELSE LEN(Number) END
    ) AS int
)
, Number

- user7011379

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- DougM · Accepted Answer

您的 WHERE 语句... 稍显复杂。

看起来，您想按整数顺序排序任何前导数字，并按其余部分排序。如果是这样，您应该将它们作为单独的子句，而不是尝试在一个子句中完成所有操作。您遇到的具体问题是，您仅允许单个数字，而不是两个或更多数字。（而且没有所谓的 two。）

以下是您的修复程序，以及使用两个单独的计算列测试进行 ORDER BY 的 SQLFiddle。（请注意，这假定 asdNumNew 的数字部分适合于 T-SQL int。如果不行，您需要调整 CAST 和第一个 ELSE 上的最大值。）

SELECT * FROM viewASD
ORDER BY 
CASE 
  WHEN ISNUMERIC(asdNumNew)=1 
  THEN CAST(asdNumNew as int)

  WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1 
  THEN CAST(
    LEFT(
      asdNumNew,
      PATINDEX('%[^0-9]%',asdNumNew) - 1
    ) as int)

  ELSE 2147483648
END, 


CASE 
  WHEN ISNUMERIC(asdNumNew)=1 
  THEN NULL

  WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1 
  THEN SUBSTRING(
      asdNumNew,
      PATINDEX('%[^0-9]%',asdNumNew) ,
      50
    ) 

  ELSE asdNumNew
END