--建立测试环境
Create Table 表(itemid varchar(10),itemDescr varchar(10))
--插入数据
insert into 表
select '01','aaa' union
select '0101','bbb' union
select '010101','ccc' union
select '010102','ddd' union
select '010103','eee' union
select '010104','fff' union
select '010105','ggg' union
select '0102','hhh' union
select '010201','iii' union
select '0103','jjj' union
select '010301','kkk' union
select '02','lll' union
select '0201','mmm' union
select '020101','nnn' union
select '020102','ooo' union
select '020103','ppp' union
select '020104','qqq' union
select '0202','rrr' union
select '020201','sss' union
select '020202','ttt'
--测试语句
select rtrim(itemid)+' '+itemDescr from 表
--删除测试环境
Drop Table 表/*
01 aaa
0101 bbb
010101 ccc
010102 ddd
010103 eee
010104 fff
010105 ggg
0102 hhh
010201 iii
0103 jjj
010301 kkk
02 lll
0201 mmm
020101 nnn
020102 ooo
020103 ppp
020104 qqq
0202 rrr
020201 sss
020202 ttt
*/
Create Table 表(itemid varchar(10),itemDescr varchar(10))
--插入数据
insert into 表
select '01','aaa' union
select '0101','bbb' union
select '010101','ccc' union
select '010102','ddd' union
select '010103','eee' union
select '010104','fff' union
select '010105','ggg' union
select '0102','hhh' union
select '010201','iii' union
select '0103','jjj' union
select '010301','kkk' union
select '02','lll' union
select '0201','mmm' union
select '020101','nnn' union
select '020102','ooo' union
select '020103','ppp' union
select '020104','qqq' union
select '0202','rrr' union
select '020201','sss' union
select '020202','ttt'
--测试语句
select rtrim(itemid)+' '+itemDescr from 表
--删除测试环境
Drop Table 表/*
01 aaa
0101 bbb
010101 ccc
010102 ddd
010103 eee
010104 fff
010105 ggg
0102 hhh
010201 iii
0103 jjj
010301 kkk
02 lll
0201 mmm
020101 nnn
020102 ooo
020103 ppp
020104 qqq
0202 rrr
020201 sss
020202 ttt
*/
Create table item
(itemid Nvarchar(20),
itemDescr Nvarchar(20)
)
--插入数据
Insert item values('01','aaa')
Insert item values('0101','bbb')
Insert item values('010101','ccc')
Insert item values('010102','ddd')
Insert item values('010103','eee')
Insert item values('010104','fff')
Insert item values('010105','ggg')
Insert item values('0102','hhh')
Insert item values('010201','iii')
Insert item values('0103','jjj')
Insert item values('010301','kkk')
Insert item values('02','lll')
Insert item values('0201','mmm')
Insert item values('020101','nnn')
Insert item values('020102','ooo')
Insert item values('020103','ppp')
Insert item values('020104','qqq')
Insert item values('0202','rrr')
Insert item values('020201','sss')
Insert item values('020202','ttt')--测试
select itemid,space((Len(itemid)-2)*2)+itemDescr from item
--删除表
Drop table item
0101 bbb
010101 ccc
010102 ddd
010103 eee
010104 fff
010105 ggg
0102 hhh
010201 iii
0103 jjj
010301 kkk
02 lll
0201 mmm
020101 nnn
020102 ooo
020103 ppp
020104 qqq
0202 rrr
020201 sss
020202 ttt
--建立测试环境
Create Table 表(itemid varchar(10),itemDescr varchar(10))
--插入数据
insert into 表
select '01','aaa' union
select '0101','bbb' union
select '010101','ccc' union
select '010102','ddd' union
select '010103','eee' union
select '010104','fff' union
select '010105','ggg' union
select '0102','hhh' union
select '010201','iii' union
select '0103','jjj' union
select '010301','kkk' union
select '02','lll' union
select '0201','mmm' union
select '020101','nnn' union
select '020102','ooo' union
select '020103','ppp' union
select '020104','qqq' union
select '0202','rrr' union
select '020201','sss' union
select '020202','ttt'
--测试语句
select itemid,
(case when len(itemid)=2 then ' '+itemDescr
when len(itemid)=4 then ' '+itemDescr
when len(itemid)=6 then ' '+itemDescr end)
as itemDescr from 表 -- select rtrim(itemid)+' '+itemDescr from 表
--删除测试环境
Drop Table 表