表A内容
T1 T2 T3
22x100
30x100
45x200希望得到的结果为(抓如X前后的内容写入T2/T3)
T1 T2 T3
22x100 22 100
300x100 300 100
4x2000 4 2000
T1 T2 T3
22x100
30x100
45x200希望得到的结果为(抓如X前后的内容写入T2/T3)
T1 T2 T3
22x100 22 100
300x100 300 100
4x2000 4 2000
调试欢乐多
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:tsDict
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tsDict')
AND type in (N'U'))
DROP TABLE tsDict
GO---->建表
create table tsDict([T1] varchar(6),[T2] sql_variant,[T3] sql_variant)
insert tsDict
select '22x100',null,null union all
select '30x100',null,null union all
select '45x200',null,null
GOupdate tsDict
set T2=substring([T1],1,CHARINDEX('x',T1)-1)
,T3=STUFF(T1,1,CHARINDEX('x',T1),'')--> 查询结果
SELECT * FROM tsDict--> 删除表格
--DROP TABLE tsDict
set t2=substring(t1,1,charindex('x',t1)-1),
t3=right(t1,len(t1)-charindex('x',t1))
(
T1 varchar(100),
T2 varchar(100),
T3 varchar(100)
)
insert into tb(T1)
select '22x100' union all
select '300x100' union all
select '4x2000'UPDATE tb
SET T2 = LEFT(T1, CHARINDEX('x', T1)-1),
T3 = RIGHT(T1, CHARINDEX('x', REVERSE(T1))-1)SELECT * FROM tb
AND type in (N'U'))
DROP TABLE tsDict
GO---->建表
create table tsDict([T1] varchar(6),[T2] sql_variant,[T3] sql_variant)
insert tsDict
select '22x100',null,null union all
select '30x100',null,null union all
select '45x200',null,null
GO
update tsDict
set T2=parsename(REPLACE(T1,'x','.'),2),
T3=parsename(REPLACE(T1,'x','.'),1)
select * from tsDict
借用菜菜数据
22x100 22 100
30x100 30 100
45x200 45 200