34,590
社区成员
发帖
与我相关
我的任务
分享
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
/*
编号 公司
---- ----
td-tl 东北设计院01
td-tl213 东北设计院002
td-tl1231 asdfsdf
td-tl123 东北设计院0003
*/