我正在嘗試檢查整個列是否為 varchar 并確保它可以轉換為浮動。我有一個像這樣的游標部分：
DECLARE @CastFailed BIT
SET @CastFailed = (SELECT SUM(CASE WHEN TRY_CAST(@ColumnName AS FLOAT) IS NULL THEN 1
ELSE 0 END) AS CastResult)
-- Look at this
PRINT @CastFailed
IF @CastFailed > 0
BEGIN
PRINT 'ERROR: ' @ColumnName ' cannot be converted to FLOAT type'
SET @HasErrors = 1
END
ELSE
BEGIN
PRINT 'The cast has passed.'
END
出于某種原因，它總是回傳 1。我已經在游標的前一部分（未顯示但在上面）中驗證了傳入的列（@ColumnName）在任何時候都不是 NULL。
我需要確定 @ColumnName 的所有 CAST 到 FLOAT 是否有效。游標在串列中回圈，一一引入 FETCH @ColumnName。我錯過了什么？
uj5u.com熱心網友回復：
簡單：
DECLARE @t TABLE (txt VARCHAR(100));
INSERT @t VALUES ('ABC123'),('100.00'),('100'),('11.222.333'),('00');
DECLARE @CastFailed BIT =
(SELECT ISNULL(MAX(1),0) FROM @t AS t WHERE TRY_CAST(t.Txt AS FLOAT) IS NULL);
SELECT CastFailed = @CastFailed;
為了獲得更好的性能...
DECLARE @t TABLE (txt VARCHAR(100));
INSERT @t VALUES ('ABC123'),('100.00'),('100'),('11.222.333'),('00');
DECLARE @CastFailed BIT =
(ISNULL((SELECT TOP(1) 1 FROM
(SELECT 1 FROM @t AS t WHERE TRY_CAST(t.Txt AS FLOAT) IS NULL) AS x(x)),0));
SELECT CastFailed = @CastFailed;
uj5u.com熱心網友回復：
這是一個可以避免游標和動態 SQL 的選項。它將動態地 UNPIVOT 您的資料并將轉換失敗的列回傳為浮動
（2008 和 2012 兼容）
例子
Declare @YourTable Table (id int,[Col1] varchar(50),[Col2] varchar(50))
Insert Into @YourTable Values
(1,'1e6','ABC') -- This Col2 will fail Conversion
,(2,'5.5','25')
,(3,'50.25','0')
Select C.Col
,Failed = count(*)
from @YourTable A
Cross Apply ( values ( convert(xml,(Select A.* for XML RAW)) ) )B(XMLData)
Cross Apply (
Select Col = xAttr.value('local-name(.)', 'varchar(100)')
,Value = xAttr.value('.','varchar(max)')
From XMLData.nodes('//@*') xNode(xAttr)
) C
Where Col in ('Col1','Col2') -- Or you can Exclude Columns ... Where Col NOT in ('id','OtherCols','ToExclude')
and try_convert(float,value) is null
Group BY C.Col
結果
Col Failed
Col2 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/447884.html
標籤：sql sql服务器 tsql sql-server-2008 sql-server-2012
下一篇：SQL中的轉換失敗