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

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

 1 --转换nvarchar字段11.11 有小数点的为int再插入另外一个表
 2 --> 测试数据：@t
 3 declare @t table
 4 ([id] int,[monthRent] NVARCHAR(50))
 5 insert @t
 6 select 1,'11.11' union all
 7 select 2,'22' union all
 8 select 3,'33.22' union all
 9 select 4,'44' union all
10 select 5,'55' union all
11 select 6,'66.66'
12 
13 SELECT * FROM @t
14 
15 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
16 
17 --再定义一个表看能否插入
18 DECLARE @a TABLE
19 ([id] INT,[monthRent] INT)
20 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
21 
22 SELECT * FROM @a

 

posted @ 2013-08-04 13:48  桦仔  阅读(577)  评论(0)    收藏  举报