错误在SQL中将数据类型Varchar转换为float

Question

我一直在尝试使用下面的查询从表中提取值，但得到错误

有一个数据类型为varchar的列，但我需要提取col3中大于500%的值

示例数据

Col1  Col2       Col3
1     15066052   555.5%
2     15066032   25.0
3     15066052   500.8%
4     15066052   450.5
5     15066041   500%

Select *
from Table1 where ([Col2]='15066052'AND Cast(Replace(LTRIM(RTRIM([Col3)) , '%','') as numeric(19,4)) > 500)

错误:

将数据类型varchar转换为数字时出错。

Answer 1

MySQL -https://www.db-fiddle.com/f/vm5neG3srXt6SbWvvST5x/0

SELECT * 
FROM (
SELECT col1, col2, REPLACE(LTRIM(RTRIM(col3)) , '%','') AS newCol
FROM Table1  
) AS innertable 
WHERE newCol > 500;

SQL Server 2019 -改编自Larnu的dbfiddle

SELECT * 
FROM (
SELECT col1, col2, REPLACE(LTRIM(RTRIM(col3)) , '%','') AS newCol
FROM Table1  
) as innertable
WHERE CAST(newCol AS numeric(19,4)) > 500;