笔记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
