SQL server从两个表中获取字符串中的最大值整数 - sql server get max value integer in a string from two tables

sql server get max value integer in a string from two tables

我有这个SQL查询mysql的工作很好。查询在2个表中找到，这是两个最大的数值，并返回mysql中的值，我必须在sql server中做同样的查询，但告诉我一个错误语法错误附近转换，预期为

查询是下一个:

SELECT MAX(CAST(RIGHT(nrt, 5) AS UNSIGNED)) 
FROM
(
  SELECT nrt from asp where nrt != ' ' and nrt is not null 
  UNION ALL 
  SELECT nrt from asp_historic where nrt != ' ' and nrt is not null
) as subQuery

有一种方法可以让这个查询运行吗?

下面是SQL Server…

的相同代码

SELECT MAX(RIGHT(ABS(nrt), 5))
    FROM
    (
      SELECT nrt from asp where nrt <> ' ' and nrt is not null 
      UNION ALL 
      SELECT nrt from asp_historic where nrt <> ' ' and nrt is not null
    ) as subQuery

您在找nrt != ' ' and nrt is not null吗?

我认为应该是OR条件而不是AND条件。

我将从完全不转换开始:

SELECT MAX(RIGHT(nrt, 5)) FROM asp WHERE nrt <> ' ' and nrt is not null 
UNION ALL 
SELECT nrt FROM asp_historic WHERE nrt M< ' ' and nrt is not null

这里假设nrt至少有5个字符。

如果不是，则转换为整数:

SELECT CAST(MAX(RIGHT(nrt, 5)) as int) FROM asp WHERE nrt <> ' ' and nrt is not null 
UNION ALL 
SELECT nrt FROM asp_historic WHERE nrt <> ' ' and nrt is not null

编辑:

这个问题似乎是关于mysql隐式转换。我建议:

SELECT MAX(CAST(LEFT(nrt + ' ', PATINDEX(nrt, '%[^0-9]%') - 1) as int))
. . .