34,590
社区成员
![](https://csdnimg.cn/release/cmsfe/public/img/topic.427195d5.png)
![](https://csdnimg.cn/release/cmsfe/public/img/me.40a70ab0.png)
![](https://csdnimg.cn/release/cmsfe/public/img/task.87b52881.png)
![](https://csdnimg.cn/release/cmsfe/public/img/share-circle.3e0b7822.png)
declare @str varchar(100),@i int
select @str='MOT-L6g锟匡靠锟匡靠锟匡靠锟匡',@i=1
while @i<=len(@str)
begin
if len(substring(@str,@i,1))<>datalength(substring(@str,@i,1))
begin
set @str=replace(@str,substring(@str,@i,1),' ')
end
set @i=@i+1
end
set @str=replace(@str,' ','')
print @str
if object_id('fn_RemoveCN') is not null drop function fn_RemoveCN
go
create function fn_RemoveCN(@s nvarchar(100))
returns nvarchar(100)
as
BEGIN
while patindex('%[吖-咗]%',@s)>0
BEGIN
set @s=stuff(@s,patindex('%[吖-咗]%',@s),1,'')
END
return @s
END
go
select dbo.fn_RemoveCN('MOT-L6g吖锟匡靠锟匡靠锟匡靠锟匡')
/*--------------------------------
MOT-L6g
(1 行受影响)
*/
create function get_str(@str varchar(100)) returns varchar(100)
as
begin
declare @rtn varchar(100)
set @rtn = left(@str , patindex(N'%[吖-咗]%' collate Chinese_PRC_CI_AS, @str) - 1)
return @rtn
end
go
select dbo.get_str('MOT-L6g锟匡靠锟匡靠锟匡靠锟匡') as result
drop function dbo.get_str
/*
result
----------------------------------------------------------------------------------------------------
MOT-L6g
(所影响的行数为 1 行)
*/
declare @a varchar(100)
set @a =N'MOT-L6g锟匡靠锟匡靠锟匡靠锟匡'
select left(@a , patindex(N'%[吖-咗]%' collate Chinese_PRC_CI_AS, @a) - 1)
/*
----------------------------------------------------------------------------------------------------
MOT-L6g
(所影响的行数为 1 行)
*/
create function func(@x nvarchar(100))
returns nvarchar(100)
as
BEGIN
while patindex('%[啊-咗]%',@x)>0
BEGIN
set @x=stuff(@x,patindex('%[啊-咗]%',@x),1,'')
END
return @x
END
go
select dbo.func('MOT-L6g锟匡靠锟匡靠锟匡靠锟匡')
--result
/*--------------------------------
MOT-L6g
(所影响的行数为 1 行)
*/