create?table?tb20111110(dwgmc?varchar(60))

insert?into?tb20111110

select?'（aa）011-钢梯T6-091(0600-8022-501)[td-tl]{asdfsdf}'?union?all

select?'011-钢梯T6-091(0600-8022-505)[td-tl213]{asdfsdf}'?union?all

select?'011-钢梯T6-091[td-tl1231]{asdfsdf}'?union?all

select?'011-钢梯T6-091(0600-8022-501)[td-tl123]{asdfsdf}'



go

alter?table?tb20111110?alter?column?dwgmc?nvarchar(60)?collate?chinese_prc_ci_as_ws???

?

go

select?

????case?when?charindex('(',dwgmc)>0?and

????charindex(')',dwgmc)>0

????then?

????substring(dwgmc,charindex('(',dwgmc)+1,charindex(')',dwgmc)-charindex('(',dwgmc)-1)

????else?'无'?end?as?dwgmc1,

    case?when?charindex('[',dwgmc)>0?and

????charindex(']',dwgmc)>0

????then?

????substring(dwgmc,charindex('[',dwgmc)+1,charindex(']',dwgmc)-charindex('[',dwgmc)-1)

????else?'无'?end?as?dwgmc2,

    case?when?charindex('{',dwgmc)>0?and

????charindex('}',dwgmc)>0

????then?

????substring(dwgmc,charindex('{',dwgmc)+1,charindex('}',dwgmc)-charindex('{',dwgmc)-1)

????else?'无'?end?as?dwgmc3



from?tb20111110?

/*

dwgmc1            dwgmc2            dwgmc3

----------------- ----------------- --------------

0600-8022-501     td-tl             asdfsdf

0600-8022-505     td-tl213          asdfsdf

无                 td-tl1231         asdfsdf

0600-8022-501     td-tl123          asdfsdf

*/

--数据引用于你上个贴中的 (☆叶子☆) 

--方法一样，其实楼主该学会举一反三的 学习下substring，charindex这2个函数

if OBJECT_ID('tb20111110') is not null drop table tb20111110

go

create table tb20111110(dwgmc varchar(60))

insert into tb20111110

select '（aa）011-钢梯T6-091(0600-8022-501)[td-tl]{东北设计院01}' union all

select '011-钢梯T6-091(0600-8022-505)[td-tl213]{东北设计院002}' union all

select '011-钢梯T6-091[td-tl1231]{asdfsdf}' union all

select '011-钢梯T6-091(0600-8022-501)[td-tl123]{东北设计院0003}'

 

select 

    case when charindex('[',dwgmc)>0 and

    charindex(']',dwgmc)>0

    then 

    substring(dwgmc,charindex('[',dwgmc)+1,charindex(']',dwgmc)-charindex('[',dwgmc)-1)

    else '无' end as 编号

    ,

     case when charindex('{',dwgmc)>0 and

    charindex('}',dwgmc)>0

    then 

    substring(dwgmc,charindex('{',dwgmc)+1,charindex('}',dwgmc)-charindex('{',dwgmc)-1)

    else '无' end as 公司

from tb20111110