create table dg(descr varchar(20))insert into dg select 'VD-1106B床头柜' union all select 'VG-0806F五门路轨' union all select 'VK-16A方几' select descr,substring(descr,1,patindex('%[吖-做]%',descr)-1) descr2 from dg/* descr descr2 -------------------- -------------------- VD-1106B床头柜 VD-1106B VG-0806F五门路轨 VG-0806F VK-16A方几 VK-16A(3 row(s) affected) */
if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(16)) insert [tb] select 'VD-1106B' union all select 'VG-0806F' union all select 'VK-16A' union all select 'VD-1106B床头柜' union all select 'VG-0806F五门路轨' union all select 'VK-16A方几' goselect left(col+'吖',patindex('%[吖-咗]%',col+'吖')-1) as col from tb/** col ------------------ VD-1106B VG-0806F VK-16A VD-1106B VG-0806F VK-16A(6 行受影响) **/
这个有点问题,对于没有汉子的记录,改进之后:create table #dg(descr varchar(20))insert into #dg select 'VD-1106B床头柜' union all select 'VG-0806F五门路轨' union all select 'VK-16A方几' union all select 'VK-16A34343'select descr,substring(descr,1,case when (patindex('%[吖-做]%',descr))=0 then LEN(descr) else patindex('%[吖-做]%',descr)-1 end) descr2 from #dgdrop table #dg
create table dg(descr varchar(20))insert into dg
select 'VD-1106B床头柜' union all
select 'VG-0806F五门路轨' union all
select 'VK-16A方几'
select descr,substring(descr,1,patindex('%[吖-做]%',descr)-1) descr2
from dg/*
descr descr2
-------------------- --------------------
VD-1106B床头柜 VD-1106B
VG-0806F五门路轨 VG-0806F
VK-16A方几 VK-16A(3 row(s) affected)
*/
go
create table [tb]([col] varchar(16))
insert [tb]
select 'VD-1106B' union all
select 'VG-0806F' union all
select 'VK-16A' union all
select 'VD-1106B床头柜' union all
select 'VG-0806F五门路轨' union all
select 'VK-16A方几'
goselect left(col+'吖',patindex('%[吖-咗]%',col+'吖')-1) as col from tb/**
col
------------------
VD-1106B
VG-0806F
VK-16A
VD-1106B
VG-0806F
VK-16A(6 行受影响)
**/
这个有点问题,对于没有汉子的记录,改进之后:create table #dg(descr varchar(20))insert into #dg
select 'VD-1106B床头柜' union all
select 'VG-0806F五门路轨' union all
select 'VK-16A方几' union all
select 'VK-16A34343'select descr,substring(descr,1,case when (patindex('%[吖-做]%',descr))=0 then LEN(descr) else patindex('%[吖-做]%',descr)-1 end) descr2
from #dgdrop table #dg