34,838
社区成员




USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t (
n NVARCHAR(20)
)
INSERT INTO t VALUES ('中文')
INSERT INTO t VALUES ('2.0')
INSERT INTO t VALUES ('1')
SELECT ltrim(CONVERT(INT,CONVERT(float,n))) AS n
FROM t WHERE TRY_CONVERT(float,n) IS NOT NULL
UNION ALL
SELECT n
FROM t WHERE TRY_CONVERT(float,n) IS NULL
/*
n
--------------------
2
1
中文
*/
IF OBJECT_ID('#t') IS NOT NULL DROP TABLE #t
GO
CREATE TABLE #t (
n NVARCHAR(20)
)
INSERT INTO #t VALUES ('中文')
INSERT INTO #t VALUES ('2.0')
INSERT INTO #t VALUES ('1')
INSERT INTO #t VALUES ('1.9')
select case when ISNUMERIC(n)=0 then n
else cast(cast(cast(n as float) as int) as nvarchar(50))
end
from #t
--------------------------------------------------
中文
2
1
1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(23))
Insert #T
select N'啊' union all
select N'2.0' union all
select N'1'
Go
--测试数据结束
SELECT CASE
WHEN ISNUMERIC(#T.col) <> 0 AND CHARINDEX('.',col)>0 THEN
SUBSTRING(col,0,CHARINDEX('.',col))
ELSE
col
END col
FROM #T;