有一表
z1
AAA你好
AAA%
AAA123
AAA(HR)
BB
BB(100105)
BB如果
CCCC*&
CCCC
DDD(100120)
DDD没有啊按字母部分来分类,字母后只会跟文字,数字,符号,括号等,操作后如下表
z1 z2
AAA你好 AAA
AAA% AAA
AAA123 AAA
AAA(HR) AAA
BB BB
BB(100105) BB
BB如果 BB
CCCC*& CCCC
CCCC CCCC
DDD(100120) DDD
DDD没有啊 DDD
z1
AAA你好
AAA%
AAA123
AAA(HR)
BB
BB(100105)
BB如果
CCCC*&
CCCC
DDD(100120)
DDD没有啊按字母部分来分类,字母后只会跟文字,数字,符号,括号等,操作后如下表
z1 z2
AAA你好 AAA
AAA% AAA
AAA123 AAA
AAA(HR) AAA
BB BB
BB(100105) BB
BB如果 BB
CCCC*& CCCC
CCCC CCCC
DDD(100120) DDD
DDD没有啊 DDD
col,
left(col,patindex('%[^a-zA-Z]%',col)-1) as col2
from
T
order by col2
insert tb select 'AAA(DMR)'
insert tb select 'AAA(FS)'
insert tb select 'AAA(MR)'
insert tb select 'AAA(HR)'
insert tb select 'AAA(100373)'
insert tb select 'AAA(600004)'
insert tb select 'BB'
insert tb select 'BB(100105)'
insert tb select 'BB(100109)'
insert tb select 'CCCC(DMR)'
insert tb select 'CCCC(MR)'
insert tb select 'CCCC'
insert tb select 'DDD(100120)'
insert tb select 'DDD(FS)'select z1,
z2=case when patindex('%[^a-zA-Z]%',z1)=0 then z1
else left(z1,patindex('%[^a-zA-Z]%',z1)-1) end
from tbdrop table tb/*
z1 z2
-------------------- --------------------
AAA(DMR) AAA
AAA(FS) AAA
AAA(MR) AAA
AAA(HR) AAA
AAA(100373) AAA
AAA(600004) AAA
BB BB
BB(100105) BB
BB(100109) BB
CCCC(DMR) CCCC
CCCC(MR) CCCC
CCCC CCCC
DDD(100120) DDD
DDD(FS) DDD(14 行受影响)*/
insert tb select 'AAA(DMR)'
insert tb select 'AAA(FS)'
insert tb select 'AAA(MR)'
insert tb select 'AAA(HR)'
insert tb select 'AAA如果'
insert tb select 'AAA(600004)'
insert tb select 'BB'
insert tb select 'BB##$#$'
insert tb select 'BB(100109)'
insert tb select 'CCCC(DMR)'
insert tb select 'CCCC(MR)'
insert tb select 'CCCC'
insert tb select 'DDD135123'
insert tb select 'DDD(FS)'select z1,
z2=case when patindex('%[^a-zA-Z]%',z1)=0 then z1
else left(z1,patindex('%[^a-zA-Z]%',z1)-1) end
from tbdrop table tb/*
z1 z2
-------------------- --------------------
AAA(DMR) AAA
AAA(FS) AAA
AAA(MR) AAA
AAA(HR) AAA
AAA如果 AAA
AAA(600004) AAA
BB BB
BB##$#$ BB
BB(100109) BB
CCCC(DMR) CCCC
CCCC(MR) CCCC
CCCC CCCC
DDD135123 DDD
DDD(FS) DDD(14 行受影响)
*/
Create table tt(name varchar(50))insert into tt select 'AAA你好'
insert into tt select 'AAA%'
insert into tt select 'AAA123'
insert into tt select 'AAA(HR)'
insert into tt select 'BB'
insert into tt select 'BB(100105)'
insert into tt select 'BB如果'
insert into tt select 'CCCC*&'
insert into tt select 'CCCC'
insert into tt select 'DDD(100120)'
insert into tt select 'DDD没有啊'select substring(name,1,patindex('%[^A-z]%',name)-1) from tt
where patindex('%[^A-z]%',name)>1
union all
select name from tt
where patindex('%[^A-z]%',name)=0AAA
AAA
AAA
AAA
BB
BB
CCCC
DDD
DDD
BB
CCCC