笔记223 转换nvarchar字段11.11 有小数点的为int再插入另外一个表

笔记223 转换nvarchar字段11.11 有小数点的为int再插入另外一个表

--转换nvarchar字段11.11 有小数点的为int再插入另外一个表
--> 测试数据：@t
declare @t table
([id] int,[monthRent] NVARCHAR(50))
insert @t
select 1,'11.11' union all
select 2,'22' union all
select 3,'33.22' union all
select 4,'44' union all
select 5,'55' union all
select 6,'66.66'

SELECT * FROM @t

SELECT id,CASE PATINDEX('%.%',RTRIM(RIGHT(monthRent,3))) WHEN 0 THEN CAST(monthRent AS INT) ELSE SUBSTRING(monthRent,1,LEN(monthRent)-3) END from @t

--再定义一个表看能否插入
DECLARE @a TABLE
([id] INT,[monthRent] INT)
INSERT @a SELECT id,CASE PATINDEX('%.%',RTRIM(RIGHT(monthRent,3))) WHEN 0 THEN CAST(monthRent AS INT) ELSE SUBSTRING(monthRent,1,LEN(monthRent)-3) END from @t

SELECT * FROM @a