27,579
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
n VARCHAR(50)
)
GO
INSERT INTO t VALUES ('1.2');
INSERT INTO t VALUES ('10/60');
INSERT INTO t VALUES ('0.5');
INSERT INTO t VALUES ('15/60');
SELECT *,CASE
WHEN CHARINDEX('/',n)>1 THEN CAST(SUBSTRING(n,1,CHARINDEX('/',n)-1) AS FLOAT)
ELSE CAST(n AS FLOAT)*60
END AS r
FROM t
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[var] CHAR(20))
Insert #T
select 1,N'1.2' union all
select 2,N'10/60' union all
select 3,N'0.5' union all
select 4,N'15/60'
Go
--测试数据结束
--更新操作
UPDATE
#T
SET
[var] = CASE
WHEN CHARINDEX('/', [var]) = 0
THEN RTRIM(CONVERT(FLOAT, [var]) * 60)
ELSE
SUBSTRING([var], 1, CHARINDEX('/', [var]) - 1)
END;
--读取测试结果
SELECT * FROM #T