--提取数字 IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^0-9]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') END RETURN @S END GO --测试 PRINT DBO.GET_NUMBER('呵呵ABC123ABC') GO --123 -------------------------------------------------------------------- --提取英文 IF OBJECT_ID('DBO.GET_STR') IS NOT NULL DROP FUNCTION DBO.GET_STR GO CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^a-z]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'') END RETURN @S END GO --测试 PRINT DBO.GET_STR('呵呵ABC123ABC') GO -------------------------------------------------------------------- --提取中文 IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL DROP FUNCTION DBO.CHINA_STR GO CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^吖-座]%',@S) > 0 SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'') RETURN @S END GO PRINT DBO.CHINA_STR('呵呵ABC123ABC') GO --------------------------------------------------------------------
参照如下做成子查询,然后用len()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('100平方米') insert into tb values('120平方米') insert into tb values('70-80平方米') go--建立如下函数(非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 --建立如下函数(非吖-做去掉。) go create function getnewstr2(@oldstr varchar(100)) returns varchar(100) as begin declare @i int set @i = 1 while @i <= len(@oldstr) if substring(@oldstr, @i, 1) like('[^吖-做]') set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '') else set @i = @i +1 return @oldstr end go select area , 非中文个数 = len(dbo.getnewstr(area)) from tb where area like('%[^a-z A-Z 0-9 -]%') /* area 非中文个数 -------------------- ----------- 100平方米 3 120平方米 3 70-80平方米 5(所影响的行数为 3 行) */ select area , 中文个数 = len(dbo.getnewstr2(area)) from tb where area like('%[^吖-做]%') /* area 中文个数 -------------------- ----------- 100平方米 3 120平方米 3 70-80平方米 3(所影响的行数为 3 行) */ drop table tb drop function dbo.getnewstr,dbo.getnewstr2
-------------------------------------------------------------------- --提取中文 IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL DROP FUNCTION DBO.CHINA_STR GO CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^吖-座]%',@S) > 0 SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'') RETURN @S END GO PRINT len(DBO.CHINA_STR('呵呵ABC123ABC')) GO/* 2 */
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--------------------------------------------------------------------
--提取英文
IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
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
呵呵ABC
应该是2个汉字 3个字母吧?
insert into tb values('100平方米')
insert into tb values('120平方米')
insert into tb values('70-80平方米')
go--建立如下函数(非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
--建立如下函数(非吖-做去掉。)
go
create function getnewstr2(@oldstr varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^吖-做]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
go
select area , 非中文个数 = len(dbo.getnewstr(area)) from tb where area like('%[^a-z A-Z 0-9 -]%')
/*
area 非中文个数
-------------------- -----------
100平方米 3
120平方米 3
70-80平方米 5(所影响的行数为 3 行)
*/
select area , 中文个数 = len(dbo.getnewstr2(area)) from tb where area like('%[^吖-做]%')
/*
area 中文个数
-------------------- -----------
100平方米 3
120平方米 3
70-80平方米 3(所影响的行数为 3 行)
*/
drop table tb
drop function dbo.getnewstr,dbo.getnewstr2
--提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
PRINT len(DBO.CHINA_STR('呵呵ABC123ABC'))
GO/*
2
*/