这个需要使用函数.参考: 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
如果只有a-z,则如下,否则自己写全需要的东西或参考一楼的函数. create table tb(area varchar(20)) insert into tb values('0.5g') insert into tb values('1cm') insert into tb values('3km') go select area1 = left(area,PATINDEX('%[a-z]%',area)-1), area1 = substring(area,PATINDEX('%[a-z]%',area) ,len(area)) from tb /* area1 area1 -------------------- -------------------- 0.5 g 1 cm 3 km(所影响的行数为 3 行) */ drop table tb
create table #(Col varchar(20)) insert into # values('0.5g') insert into # values('1cm') insert into # values('3km') insert into # values('10hfhfff') goselect col1 = left(col, PATINDEX('%[a-z]%',col)-1), col2 = substring(col,PATINDEX('%[a-z]%',col) ,len(col)) from #select col1 = LEFT(col, PATINDEX('%[a-z]%',col)-1), col2 = RIGHT(col,LEN(col)- PATINDEX('%[a-z]%',col)+1) from # GoDROP TABLE #
找一个提取汉字 或提取字符串的函数。就可以了 --取数字 CREATE function dbo.F_Get_No ( @No varchar(100) ) RETURNS INT AS BEGIN WHILE PATINDEX('%[^0-9]%',@No)>0 BEGIN SET @No=STUFF(@No,PATINDEX('%[^0-9]%',@No),1,'') END RETURN CONVERT(INT,@No) END --取汉字 create function dbo.F_Get_Letter ( @Letter NVARCHAR(100) ) RETURNS NVARCHAR(100) AS BEGIN WHILE PATINDEX('%[^A-Z]%',@Letter)>0 BEGIN SET @Letter=STUFF(@Letter,PATINDEX('%[^A-Z]%',@Letter),1,'') END RETURN @Letter END
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
create table tb(area varchar(20))
insert into tb values('0.5g')
insert into tb values('1cm')
insert into tb values('3km')
go
select area1 = left(area,PATINDEX('%[a-z]%',area)-1),
area1 = substring(area,PATINDEX('%[a-z]%',area) ,len(area))
from tb
/*
area1 area1
-------------------- --------------------
0.5 g
1 cm
3 km(所影响的行数为 3 行)
*/
drop table tb
insert into # values('0.5g')
insert into # values('1cm')
insert into # values('3km')
insert into # values('10hfhfff')
goselect col1 = left(col, PATINDEX('%[a-z]%',col)-1),
col2 = substring(col,PATINDEX('%[a-z]%',col) ,len(col))
from #select col1 = LEFT(col, PATINDEX('%[a-z]%',col)-1),
col2 = RIGHT(col,LEN(col)- PATINDEX('%[a-z]%',col)+1)
from #
GoDROP TABLE #
RIGHT(col,LEN(col)-PATINDEX('%[a-z]%',col)+1)
--取数字
CREATE function dbo.F_Get_No
(
@No varchar(100)
)
RETURNS INT
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@No)>0
BEGIN
SET @No=STUFF(@No,PATINDEX('%[^0-9]%',@No),1,'')
END
RETURN CONVERT(INT,@No)
END --取汉字
create function dbo.F_Get_Letter
(
@Letter NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^A-Z]%',@Letter)>0
BEGIN
SET @Letter=STUFF(@Letter,PATINDEX('%[^A-Z]%',@Letter),1,'')
END
RETURN @Letter
END