--建立如下函数 go create function getnewstr(@oldstr varchar(100)) returns varchar(100) as begin declare @i int set @i = 1 while @i <= len(@oldstr) if substring(@oldstr, @i, 1) like('[^a-z,A-Z,吖-咗]') set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '') else set @i = @i +1 return @oldstr end goselect area = dbo.getnewstr(area) from tb where area like('%[^a-z,A-Z,吖-咗]%')
--建立如下函数 create table tb(area varchar(20)) insert into tb values('100平方米') insert into tb values('dsalfk3445j中国') insert into tb values('dasf@#$%%%%美国') go go create function getnewstr(@oldstr varchar(100)) returns varchar(100) as begin declare @i int set @i = 1 while @i <= len(@oldstr) if substring(@oldstr, @i, 1) like('[^a-z,A-Z,吖-咗]') set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '') else set @i = @i +1 return @oldstr end goselect area area_old , area_new = dbo.getnewstr(area) from tb where area like('%[^a-z,A-Z,吖-咗]%')drop table tb drop function dbo.getnewstr/*area_old area_new -------------------- ---------------------------------------------------------------------------------------------------- 100平方米 平方米 dsalfk3445j中国 dsalfkj中国 dasf@#$%%%%美国 dasf美国(所影响的行数为 3 行) */
declare @s nvarchar(1000) SET @s='左轲工,成功2039asldkjf32AA34laksd,,""' while patindex('%[^啊-咗a-z]%',@s)>0 BEGIN set @s=stuff(@s,patindex('%[^啊-咗a-z]%',@s),1,'') END select @s--result /*---------------------------- 左轲工成功asldkjfAAlaksd(所影响的行数为 1 行)*/
学习了 UNICODE中的中文分布究竟是哪个区间的呢? 吖-咗? 还是 啊-咗 或是 啊-座
数据用大乌龟的 alter table tb(area varchar(20)) insert into tb values('100平方米') insert into tb values('dsalfk3445j中国') insert into tb values('dasf@#$%%%%美国') create function dbo.fc_str(@str varchar(100)) returns varchar(100) as begin declare @i int set @i=1 while @i<len(@str) begin if(substring(@str,@i,1) like('[^a-z,A-Z,吖-咗]')) begin set @str=stuff(@str,@i,1,'') end else begin set @i=@i+1 endend return @str endselect dbo.fc_str(area) from tb
go
create function getnewstr(@oldstr varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^a-z,A-Z,吖-咗]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
goselect area = dbo.getnewstr(area) from tb where area like('%[^a-z,A-Z,吖-咗]%')
create table tb(area varchar(20))
insert into tb values('100平方米')
insert into tb values('dsalfk3445j中国')
insert into tb values('dasf@#$%%%%美国')
go
go
create function getnewstr(@oldstr varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^a-z,A-Z,吖-咗]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
goselect area area_old , area_new = dbo.getnewstr(area) from tb where area like('%[^a-z,A-Z,吖-咗]%')drop table tb
drop function dbo.getnewstr/*area_old area_new
-------------------- ----------------------------------------------------------------------------------------------------
100平方米 平方米
dsalfk3445j中国 dsalfkj中国
dasf@#$%%%%美国 dasf美国(所影响的行数为 3 行)
*/
while patindex('%[^啊-咗a-z]%',@s)>0
BEGIN
set @s=stuff(@s,patindex('%[^啊-咗a-z]%',@s),1,'')
END
select @s--result
/*----------------------------
左轲工成功asldkjfAAlaksd(所影响的行数为 1 行)*/
UNICODE中的中文分布究竟是哪个区间的呢?
吖-咗? 还是 啊-咗 或是 啊-座
alter table tb(area varchar(20))
insert into tb values('100平方米')
insert into tb values('dsalfk3445j中国')
insert into tb values('dasf@#$%%%%美国') create function dbo.fc_str(@str varchar(100))
returns varchar(100)
as
begin
declare @i int
set @i=1
while @i<len(@str)
begin
if(substring(@str,@i,1) like('[^a-z,A-Z,吖-咗]'))
begin
set @str=stuff(@str,@i,1,'')
end
else
begin
set @i=@i+1
endend
return @str
endselect dbo.fc_str(area) from tb