34,594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE (
ftbl VARCHAR(50),
qua INT
)
INSERT INTO @t
SELECT '1/2',5
UNION ALL SELECT '1/3',20
UNION ALL SELECT '1/5',30
UNION ALL SELECT '1/2',10
UNION ALL SELECT '12/600',10
SELECT
*,
SUBSTRING(ftbl,1,CHARINDEX('/',ftbl)-1) AS [分子]
,
SUBSTRING(ftbl,CHARINDEX('/',ftbl)+1,LEN(ftbl)) AS [分母]
,
CAST(SUBSTRING(ftbl,1,CHARINDEX('/',ftbl)-1) AS NUMERIC(18,5))
/
CAST(SUBSTRING(ftbl,CHARINDEX('/',ftbl)+1,LEN(ftbl)) AS NUMERIC(18,5)) AS [化为小数]
FROM @t
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t (
ftbl VARCHAR(50),
qua INT
)
INSERT INTO t(ftbl,qua)
SELECT '1/2',5
UNION ALL SELECT '1/3',20
UNION ALL SELECT '1/5',30
UNION ALL SELECT '1/2',10
UNION ALL SELECT '1/6',10
--1. 增加一个计算列
ALTER TABLE t ADD ftbl_numeric AS
CAST(LEFT(ftbl,CHARINDEX(ftbl,'/')+1) AS NUMERIC(18,5))
/
CAST(RIGHT(ftbl,CHARINDEX(ftbl,'/')+1) AS NUMERIC(18,5))
PERSISTED
--2. 查看
SELECT * FROM t
DECLARE @t TABLE (
ftbl VARCHAR(50),
qua INT
)
INSERT INTO @t
SELECT '1/2',5
UNION ALL SELECT '1/3',20
UNION ALL SELECT '1/5',30
UNION ALL SELECT '1/2',10
UNION ALL SELECT '1/6',10
SELECT
CAST(LEFT(ftbl,CHARINDEX(ftbl,'/')+1) AS NUMERIC(18,5))
/
CAST(RIGHT(ftbl,CHARINDEX(ftbl,'/')+1) AS NUMERIC(18,5))
FROM @t
/*
0.50000000000000000000
0.33333333333333333333
0.20000000000000000000
0.50000000000000000000
0.16666666666666666666
*/