sql server从两个表中获取字符串中的最大值整数

Question

i have this sql query for mysql that works well. the query finds in 2 tables wich is the max numeric value from both and returns the value good in mysql, i have to do the same query in sql server but tells me an error syntax error near cast, expects as

the query is the next :

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

there is a way to do that the this query runs?

Answer 1

I would start with no conversion at all:

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 

This assumes that nrt has at least 5 characters.

If not, convert to an integer:

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 

EDIT:

This question appears to be about MySQLs implicit conversions. I might suggestion:

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