建议你去oracle版问.如果是sql server,可参考如下: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 goselect 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
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
goselect 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