由于您没有解析功能,并且使用的是SQL Server 2008,因此这是一种可行的方法。效率不高,但选择不多。你可以试试看。。catch模式编写自己的函数来验证十进制数。
DECLARE @Values TABLE(
ID INT IDENTITY(1,1)
, [Value] VARCHAR(20)
)
INSERT INTO @Values SELECT '22.1'
INSERT INTO @Values SELECT '22.1 sdfsfds'
DECLARE @GoodValues TABLE(ID INT)
DECLARE @BadValues TABLE(ID INT)
DECLARE @ID INT
DECLARE @Value VARCHAR(20)
DECLARE @ValueAsDecimal DECIMAL
DECLARE tryparse_cursor CURSOR FOR
SELECT ID, [Value]
FROM @Values
OPEN tryparse_cursor
FETCH NEXT FROM tryparse_cursor
INTO @ID, @Value
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @ValueAsDecimal=CAST(@Value AS DECIMAL);
INSERT INTO @GoodValues SELECT @ID;
END TRY
BEGIN CATCH
INSERT INTO @BadValues SELECT @ID;
END CATCH
FETCH NEXT FROM tryparse_cursor
INTO @ID, @Value
END
CLOSE tryparse_cursor;
DEALLOCATE tryparse_cursor;
select * from @GoodValues
select * from @BadValues