27,579
社区成员
发帖
与我相关
我的任务
分享
create table tb(area varchar(20))
insert into tb values('100平方米')
insert into tb values('120平方米')
insert into tb values('70-80平方米')
go
--方法一
[吖-咗]
select left(area,PATINDEX('%[吖-做]%',area)-1) area from tb
/*
area
--------------------
100
120
70-80
(所影响的行数为 3 行)
*/
--方法二
--建立如下函数(非a-z A-Z 0-9 - , 以及中文字符之外的字符删除)
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,0-9,-]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
go
select area = dbo.getnewstr(area) from tb where area like('%[^a-z,A-Z,0-9,-]%')
/*
area
--------------------
100
120
70-80
(所影响的行数为 3 行)
*/
drop table tb
drop function dbo.getnewstr
-- 举个例子.
DECLARE @i int=1,@len int,@num int
DECLARE @address varchar(500),@c char(1)
set @len=len(@address)
while (@i<= @len)
begin
set @c=substring(@Address, @i, 1)
set @num = ascii(@c)
if ((@num > 47 and @num < 58) or (@num> 64 and @num<123))
begin
相应的处理语句
end
else
begin
相应的处理语句
end
set @i=@i+1
end