create table test(BH varchar(50))
insert test select '01-1'
union all select '02M'
union all select '03毛-A'
union all select 'CI31.7I下沖(R400)'
union all select 'CI45下沖(R500)樣品模'
union all select 'CI6.6/9.1ACT下沖(R200)'
select BH=case when PATINDEX('%[^0-9a-zA-Z./-]%', BH)>0 then
left(BH,PATINDEX('%[^0-9a-zA-Z./-]%', BH)-1) else BH end from test
create table xyz(bh nvarchar(100))
insert xyz select '01-1'
union all select '02M--大家好'
union all select '03毛-A'
go
create function fun_xyz(@v nvarchar(100))
returns nvarchar(100)
as
begin
declare @i int,@x nvarchar(100)
set @x=''
set @i=1
while @i<=len(@v)
begin
select @x=@x+case when datalength(cast(substring(@v,@i,1) as varchar) collate Chinese_PRC_BIN)=2 then '' else substring(@v,@i,1) end
set @i=@i+1
end
return @x
end
go
select dbo.fun_xyz(bh) from xyz