你的WHERE语句非常复杂。
看起来您想按整数顺序按任何前导数字排序，然后按余数排序。如果是这样的话，你应该把它作为单独的条款来做，而不是试图把它全部放在一起。你面临的具体问题是，你只允许一个数字，而不是两个或更多。（还有No such thing as two。）
这是您的修复，以及SQLFiddle，使用两个单独的计算列测试ORDERBY。（请注意，这假设asdNumNew的数字部分适合T-SQL int。如果不适合，则需要调整第一个ELSE上的CAST和最大值。）

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

如果字符串中的所有数字都相当小，例如不超过10位，则可以将字符串中的数字扩展为正好10位：
123A->0000000 123A

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)

我有一些类似的东西，但可能有破折号作为前导字符和尾随空格。这个代码对我有效。

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

试试这个

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

对我有用的是我将数字和字母部分分开，然后根据字母排序，然后根据数字排序：

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;