AO-0.25-4
AO-0.37-4
Y2-1.5-2(1004喷塑)
Y2-1.5-2(400V.50HZ)
Y2-1.5-2(415V.50HZ)
Y2-1.5-2(B5全圆盖)
Y2-1.5-2(不喷不订)
Y2-1.5-2(高温轴承)
Y2-1.5-2(加150度PTC)
Y2-1.5-2(菱型PEM-SEL)
Y2-1.5-2(铝壳)
Y2-1.5-2(铝壳)
Y2-1.5-2(铭牌订IP54)
Y-0.55-4
Y-0.55-4
Y-0.55-4
YEJ2-0.55-4(71机座)(快速整流器)
Y2-45-4(400V/50HZ)(菱形PEM-SEL)如何去除第一个'-’及'-'前的字符和‘()’及‘()’中的内容有些‘()’前还有空格变成如下:
0.25-4
0.37-4
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
0.55-4
0.55-4
0.55-4
0.55-4
45-4
AO-0.37-4
Y2-1.5-2(1004喷塑)
Y2-1.5-2(400V.50HZ)
Y2-1.5-2(415V.50HZ)
Y2-1.5-2(B5全圆盖)
Y2-1.5-2(不喷不订)
Y2-1.5-2(高温轴承)
Y2-1.5-2(加150度PTC)
Y2-1.5-2(菱型PEM-SEL)
Y2-1.5-2(铝壳)
Y2-1.5-2(铝壳)
Y2-1.5-2(铭牌订IP54)
Y-0.55-4
Y-0.55-4
Y-0.55-4
YEJ2-0.55-4(71机座)(快速整流器)
Y2-45-4(400V/50HZ)(菱形PEM-SEL)如何去除第一个'-’及'-'前的字符和‘()’及‘()’中的内容有些‘()’前还有空格变成如下:
0.25-4
0.37-4
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
0.55-4
0.55-4
0.55-4
0.55-4
45-4
declare @table table (col varchar(31))
insert into @table
select 'AO-0.25-4' union all
select 'AO-0.37-4' union all
select 'Y2-1.5-2(1004喷塑)' union all
select 'Y2-1.5-2(400V.50HZ)' union all
select 'Y2-1.5-2(415V.50HZ)' union all
select 'Y2-1.5-2(B5全圆盖)' union all
select 'Y2-1.5-2(不喷不订)' union all
select 'Y2-1.5-2(高温轴承)' union all
select 'Y2-1.5-2(加150度PTC)' union all
select 'Y2-1.5-2(菱型PEM-SEL)' union all
select 'Y2-1.5-2(铝壳)' union all
select 'Y2-1.5-2(铝壳)' union all
select 'Y2-1.5-2(铭牌订IP54)' union all
select 'Y-0.55-4' union all
select 'Y-0.55-4' union all
select 'Y-0.55-4' union all
select 'YEJ2-0.55-4(71机座)(快速整流器)' union all
select 'Y2-45-4(400V/50HZ)(菱形PEM-SEL)' union all
select 'AO-0.25-4' union all
select 'AO-0.37-4' union all
select 'Y2-1.5-2(1004喷塑)' union all
select 'Y2-1.5-2(400V.50HZ)' union all
select 'Y2-1.5-2(415V.50HZ)' union all
select 'Y2-1.5-2(B5全圆盖)' union all
select 'Y2-1.5-2(不喷不订)' union all
select 'Y2-1.5-2(高温轴承)' union all
select 'Y2-1.5-2(加150度PTC)' union all
select 'Y2-1.5-2(菱型PEM-SEL)' union all
select 'Y2-1.5-2(铝壳)' union all
select 'Y2-1.5-2(铝壳)' union all
select 'Y2-1.5-2(铭牌订IP54)' union all
select 'Y-0.55-4' union all
select 'Y-0.55-4' union all
select 'Y-0.55-4' union all
select 'YEJ2-0.55-4(71机座)(快速整流器)' union all
select 'Y2-45-4(400V/50HZ)(菱形PEM-SEL)'--先把空格去掉,全角转成半角,统计格式后就好处理了。
update @table set col=replace(col,' ','')
update @table set col=replace(col,'(','(')
update @table set col=replace(col,')',')')
update @table set col=replace(col,'—','-')
select * from @table把数据库里面的数据搞得各种格式都有,太乱了,统一一下可以避免很多麻烦。
declare @table table (col varchar(31))
insert into @table
select 'AO-0.25-4' union all
select 'AO-0.37-4' union all
select 'Y2-1.5-2(1004喷塑)' union all
select 'Y2-1.5-2(400V.50HZ)' union all
select 'Y2-1.5-2(415V.50HZ)' union all
select 'Y2-1.5-2(B5全圆盖)' union all
select 'Y2-1.5-2(不喷不订)' union all
select 'Y2-1.5-2(高温轴承)' union all
select 'Y2-1.5-2(加150度PTC)' union all
select 'Y2-1.5-2(菱型PEM-SEL)' union all
select 'Y2-1.5-2(铝壳)' union all
select 'Y2-1.5-2(铝壳)' union all
select 'Y2-1.5-2(铭牌订IP54)' union all
select 'Y-0.55-4' union all
select 'Y-0.55-4' union all
select 'Y-0.55-4' union all
select 'YEJ2-0.55-4(71机座)(快速整流器)' union all
select 'Y2-45-4(400V/50HZ)(菱形PEM-SEL)' union all
select 'AO-0.25-4' union all
select 'AO-0.37-4' union all
select 'Y2-1.5-2(1004喷塑)' union all
select 'Y2-1.5-2(400V.50HZ)' union all
select 'Y2-1.5-2(415V.50HZ)' union all
select 'Y2-1.5-2(B5全圆盖)' union all
select 'Y2-1.5-2(不喷不订)' union all
select 'Y2-1.5-2(高温轴承)' union all
select 'Y2-1.5-2(加150度PTC)' union all
select 'Y2-1.5-2(菱型PEM-SEL)' union all
select 'Y2-1.5-2(铝壳)' union all
select 'Y2-1.5-2(铝壳)' union all
select 'Y2-1.5-2(铭牌订IP54)' union all
select 'Y-0.55-4' union all
select 'Y-0.55-4' union all
select 'Y-0.55-4' union all
select 'YEJ2-0.55-4(71机座)(快速整流器)' union all
select 'Y2-45-4(400V/50HZ)(菱形PEM-SEL)'--先把空格去掉,全角转成半角
update @table set col=replace(col,' ','')
update @table set col=replace(col,'(','(')
update @table set col=replace(col,')',')')
update @table set col=replace(col,'—','-')select substring(col,charindex('-',col)+1,
case when charindex('(',col)=0 then len(col) else charindex('(',col)-1 end
-charindex('-',col) ) as col from @table
/*
col
-------------------------------
0.25-4
0.37-4
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
0.55-4
0.55-4
0.55-4
0.55-4
45-4
0.25-4
0.37-4
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
1.5-2
0.55-4
0.55-4
0.55-4
0.55-4
45-4
*/
create table t1
(
val varchar(100)
)
insert into t1 (val) values ('AO-0.25-4'),
('AO-0.37-4'),
('Y2-1.5-2(1004喷塑)'),
('Y2-1.5-2(400V.50HZ)'),
('Y2-1.5-2(415V.50HZ)'),
('Y2-1.5-2(B5全圆盖)'),
('Y2-1.5-2(不喷不订)'),
('Y2-1.5-2(高温轴承)'),
('Y2-1.5-2(加150度PTC)'),
('Y2-1.5-2(菱型PEM-SEL)'),
('Y2-1.5-2(铝壳)'),
('Y2-1.5-2(铝壳)'),
('Y2-1.5-2(铭牌订IP54)'),
('Y-0.55-4'),
('Y-0.55-4'),
('Y-0.55-4'),
('YEJ2-0.55-4(71机座)(快速整流器)'),
('Y2-45-4(400V/50HZ)(菱形PEM-SEL)')
select * from t1select SUBSTRING(val,CHARINDEX('-',val)+1,case when charindex('(',val)>0 then charindex('(',val)-CHARINDEX('-',val)-1
when CHARINDEX('(',val)>0 then charindex('(',val)-CHARINDEX('-',val)-1
else len(val)-CHARINDEX('-',val) end)
from t1