请dawugui(潇洒老乌龟) 再帮一下忙,谢谢，其它朋友也可进入
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(bh varchar(10),mc varchar(50))
insert into tb(bh,mc) values( '001 ', 'xyz/ym/zx32s(50/25/25) ')
insert into tb(bh,mc) values( '002 ', 'xb/yss45s(65/35) ')
insert into tb(bh,mc) values( '003 ', 'cx40s ' )
go
alter table tb add mc1 varchar(50)
alter table tb add mc2 varchar(50)
go
update tb
set mc1 = left(mc , charindex( '( ',mc) -1),
mc2 = substring(mc , charindex( '( ' , mc) + 1 , charindex( ') ' , mc) - charindex( '( ' , mc) - 1)
where charindex( '( ' , mc) > 0
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
id = identity(int,1,1),
A.bh,
mc1 = SUBSTRING(A.mc1, B.ID, CHARINDEX( '/ ', A.mc1 + '/ ', B.ID) - B.ID)
into test1
FROM tb A, # B
WHERE SUBSTRING( '/ ' + A.mc1, B.ID, 1) = '/ '
ORDER BY 1,2
SELECT
id = identity(int,1,1),
A.bh,
mc2 = SUBSTRING(A.mc2, B.ID, CHARINDEX( '/ ', A.mc2 + '/ ', B.ID) - B.ID)
into test2
FROM tb A, # B
WHERE SUBSTRING( '/ ' + A.mc2, B.ID, 1) = '/ '
ORDER BY 1,2
select a.bh , left(a.mc1,1) mc1 , b.mc2 from test1 a,test2 b where a.id = b.id
drop table tb,#,test1,test2
/*
bh mc1 mc2
---------- ---- --------------------------------------------------
001 x 50
001 y 25
001 z 25
002 x 65
002 y 35
（所影响的行数为 5 行）
*/
刚才试了一下上述代码只取了一个字母，假如多个字母如何处理，谢谢
[解决办法]
完整代碼
create table tb(bh varchar(10),mc varchar(50))
insert into tb(bh,mc) values( '001 ', 'xyz/ym/zx32s(50/25/25) ')
insert into tb(bh,mc) values( '002 ', 'xb/yss45s(65/35) ')
insert into tb(bh,mc) values( '003 ', 'cx40s ' )
go
alter table tb add mc1 varchar(50)
alter table tb add mc2 varchar(50)
go
update tb
set mc1 = left(mc , charindex( '( ',mc) -1),
mc2 = substring(mc , charindex( '( ' , mc) + 1 , charindex( ') ' , mc) - charindex( '( ' , mc) - 1)
where charindex( '( ' , mc) > 0
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
id = identity(int,1,1),
A.bh,
mc1 = SUBSTRING(A.mc1, B.ID, CHARINDEX( '/ ', A.mc1 + '/ ', B.ID) - B.ID)
into test1
FROM tb A, # B
WHERE SUBSTRING( '/ ' + A.mc1, B.ID, 1) = '/ '
ORDER BY 1,2
SELECT
id = identity(int,1,1),
A.bh,
mc2 = SUBSTRING(A.mc2, B.ID, CHARINDEX( '/ ', A.mc2 + '/ ', B.ID) - B.ID)
into test2
FROM tb A, # B
WHERE SUBSTRING( '/ ' + A.mc2, B.ID, 1) = '/ '
ORDER BY 1,2
select a.bh , mc1 , b.mc2 from test1 a,test2 b where a.id = b.id
drop table tb,#,test1,test2
/*
bh mc1 mc2
---------- ---- --------------------------------------------------
001xyz50
001ym25
001zx32s25
002xb65
002yss45s35
（所影响的行数为 5 行）
*/
[解决办法]
create table tb(bh varchar(10),mc varchar(50))
insert into tb(bh,mc) values( '001 ', 'xyz/ym/zx32s(50/25/25) ')
insert into tb(bh,mc) values( '002 ', 'xb/yss45s(65/35) ')
insert into tb(bh,mc) values( '003 ', 'cx40s ' )
go
alter table tb add mc1 varchar(50)
alter table tb add mc2 varchar(50)
go
--select * from tb
update tb
set mc1 = left(mc , charindex( '( ',mc) -1),
mc2 = substring(mc , charindex( '( ' , mc) + 1 , charindex( ') ' , mc) - charindex( '( ' , mc) - 1)
where charindex( '( ' , mc) > 0
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
id = identity(int,1,1),
A.bh,
mc1 = SUBSTRING(A.mc1, B.ID, CHARINDEX( '/ ', A.mc1 + '/ ', B.ID) - B.ID)
into test1
FROM tb A, # B
WHERE SUBSTRING( '/ ' + A.mc1, B.ID, 1) = '/ '
ORDER BY 1,2
SELECT
id = identity(int,1,1),
A.bh,
mc2 = SUBSTRING(A.mc2, B.ID, CHARINDEX( '/ ', A.mc2 + '/ ', B.ID) - B.ID)
into test2
FROM tb A, # B
WHERE SUBSTRING( '/ ' + A.mc2, B.ID, 1) = '/ '
ORDER BY 1,2
select a.bh ,left(mc1,2) , b.mc2 from test1 a,test2 b where a.id = b.id
drop table tb,#,test1,test2
这样的话只取了mc1前两个字母,不知道你是不是要这样的?
