--测试 declare @test table (c1 varchar(100)) insert @test select 'a座位b ' union all select '斯b巴c ' union all select 'c旷d考 ' union all select '中d国e热敏 ' union all select '萨斯ef ' --原始数据 select * from @test /* a座位b 斯b巴c c旷d考 中d国e热敏 萨斯ef */ --替换中文字符 while exists (select 1 from @test where patindex( '%[吖-咗]% ', c1 COLLATE Chinese_PRC_CI_AS) > 0) begin update @test set c1 = stuff(c1, patindex( '%[吖-咗]% ', c1), 1, ' ') where patindex( '%[吖-咗]% ', c1 COLLATE Chinese_PRC_CI_AS) > 0 end --最终数据 select * from @test /* ab bc cd de ef */
IF OBJECT_ID ( 'F_Str2' ) IS NOT NULL DROP FUNCTION F_Str2goCREATE FUNCTION dbo . F_Str2 ( @Str NVARCHAR ( 4000 ), -- 需要处理的字符串 @Condition nvarchar ( 1000 ) -- 保留字符串如 :N'%[a-zA-Z]%', 注意这里需要有 [])RETURNS NVARCHAR ( 4000 )ASBEGINDECLARE @s NVARCHAR ( 4000 ), @s2 NVARCHAR ( 4000 ) , @s3 NVARCHAR ( 2 ), @s4 NVARCHAR ( 2 ) , @a NVARCHAR ( 4000 ), @b NVARCHAR ( 4000 ) , @i INT , @j INT , @k INT SELECT @s = '' , @s2 = '' , @i = 1 , @k = len ( @Str ), @j = CEILING ( @k * 1.0 / 2 ) SELECT @a =LEFT( @Str , @j ), @b =RIGHT( @Str , @k - @j ) WHILE @i <= @j begin SELECT @s3 = SUBSTRING ( @a , @i , 1 ), @s4 = SUBSTRING ( @b , @i , 1 ) if @s3 LIKE @Condition SET @s = @s + @s3 if @s4 LIKE @Condition SET @s2 = @s2 + @s4 SET @i = @i + 1 endRETURN @s + @s2END GO
用以上函數處理,調用函數方法 declare @test table (c1 varchar(100)) insert @test select 'a座位b ' union all select '斯b巴c ' union all select 'c旷d考 ' union all select '中d国e热敏 ' union all select '萨斯ef ' SELECT dbo.f_str1(c1,N'%[a-zA-Z]%') FROM @test/* ab bc cd de ef */
你可以参考如下的函数,不过你这个貌似要麻烦些. 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
--中文过滤 declare @str varchar(max),@len int,@con int,@str1 varchar(max) set @str='NAL-JH08-透明-G628-黑盖' set @len=LEN(@str) set @str1='' set @con=1 while @con <=@len begin if datalength(substring(@str,@con,1))=1 begin set @str1=@str1+substring(@str,@con,1) set @con=@con+1 end else begin break; end end SET @str1=LEFT(@str1,LEN(@str1)-1) print @str1/*---------------- NAL-JH08(1 行受影响)*/
--演示环境 CREATE TABLE TEST(NAME VARCHAR(100)) INSERT INTO TEST(NAME) SELECT 'NAL-JH08-透明-G628-黑盖' UNION ALL SELECT 'NAL-透明-G628-黑盖' UNION ALL SELECT 'JH08-透明-G628-黑盖' UNION ALL SELECT 'NAL-JH08-G628透明-G628-黑盖'----函数 CREATE FUNCTION GET_NEWSTR(@oldstr VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN declare @len int,@con int,@newstr varchar(max) set @len=LEN(@oldstr) set @newstr='' set @con=1 while @con <=@len begin if datalength(substring(@oldstr,@con,1))=1 begin set @newstr=@newstr+substring(@oldstr,@con,1) set @con=@con+1 end else begin break; end end SET @newstr=LEFT(@newstr,LEN(@newstr)-1) return @newstr END--查询 SELECT NAME =dbo.GET_NEWSTR(NAME) FROM TEST /* NAME ---------------------------------------------------------------------------------------------------- NAL-JH08 NAL JH08 NAL-JH08-G62(4 行受影响)*/ -- DROP TABLE TEST DROP FUNCTION dbo.GET_NEWSTR
--更新 UPDATE TEST SET NAME=dbo.GET_NEWSTR(NAME) -- SELECT * FROM TEST/* NAME ---------------------------------------------------------------------------------------------------- NAL-JH08 NAL JH08 NAL-JH08-G62(4 行受影响)*/
--提取数字 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 -------------------------------------------------------------------- --过滤重复字符 IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULL DROP FUNCTION DBO.DISTINCT_STR GO CREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50)) RETURNS VARCHAR(100) AS BEGIN IF @S IS NULL RETURN(NULL) DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50) IF LEFT(@S,1)<>@SPLIT SET @S = @SPLIT+@S IF RIGHT(@S,1)<>@SPLIT SET @S = @S+@SPLIT WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1 BEGIN SET @INDEX = CHARINDEX(@SPLIT,@S) SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT))) IF @NEW IS NULL SET @NEW = ISNULL(@NEW,'')+@TEMP ELSE SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT WHILE CHARINDEX(@TEMP,@S)>0 BEGIN SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'') END END RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1) END GO PRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',') --A,B,C GO -------------------------------------------------------------------- --过滤重复字符2 IF OBJECT_ID('DBO.DISTINCT_STR2') IS NOT NULL DROP FUNCTION DBO.DISTINCT_STR2 GO CREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(8000)) RETURNS VARCHAR(100) AS BEGIN IF @S IS NULL RETURN(NULL) DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50) WHILE LEN(@S)>0 BEGIN SET @NEW=ISNULL(@NEW,'')+LEFT(@S,1) SET @S=REPLACE(@S,LEFT(@S,1),'') END RETURN @NEW END GO SELECT DBO.DISTINCT_STR2('AABCCD') --ABCD GO-------------------------------------------------------------------- IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL DROP FUNCTION DBO.SPLIT_STR GO CREATE FUNCTION DBO.SPLIT_STR( @S varchar(8000), --包含多个数据项的字符串 @INDEX int, --要获取的数据项的位置 @SPLIT varchar(10) --数据分隔符 ) RETURNS VARCHAR(100) AS BEGIN IF @S IS NULL RETURN(NULL) DECLARE @SPLITLEN int SELECT @SPLITLEN=LEN(@SPLIT+'A')-2 WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0 SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'') RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),'')) END GO PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|') -- GO
針對樓主的數據這樣寫 USE tempdbgo CREATE FUNCTION fn_Str( @Str nvarchar(1000) ) RETURNS nvarchar(1000) AS begin DECLARE @i INT,@s NVARCHAR(1000) SELECT @i=PATINDEX('%[a-zA-Z]%',@Str),@s='' IF @i=0 RETURN ''
WHILE @i>0 BEGIN IF SUBSTRING(@str,@i,1) NOT LIKE '%[a-zA-Z0-9-]%' BREAK SELECT @s=@s+SUBSTRING(@str,@i,1) SET @i=@i+1 END WHILE RIGHT(@s,1)='-' SELECT @s=LEFT(@s,LEN(@s)-1) RETURN @s end go DECLARE @str sysname SET @str=N'NAL-JH08-透明-G628-黑盖'SELECT dbo.fn_Str(@str)/* NAL-JH08 */
declare @test table (c1 varchar(100))
insert @test
select 'a座位b ' union all
select '斯b巴c ' union all
select 'c旷d考 ' union all
select '中d国e热敏 ' union all
select '萨斯ef ' --原始数据
select * from @test
/*
a座位b
斯b巴c
c旷d考
中d国e热敏
萨斯ef
*/ --替换中文字符
while exists (select 1 from @test where patindex( '%[吖-咗]% ', c1 COLLATE Chinese_PRC_CI_AS) > 0)
begin
update @test set c1 = stuff(c1, patindex( '%[吖-咗]% ', c1), 1, ' ') where patindex( '%[吖-咗]% ', c1 COLLATE Chinese_PRC_CI_AS) > 0
end --最终数据
select * from @test
/*
ab
bc
cd
de
ef
*/
declare @test table (c1 varchar(100))
insert @test
select 'a座位b ' union all
select '斯b巴c ' union all
select 'c旷d考 ' union all
select '中d国e热敏 ' union all
select '萨斯ef ' SELECT dbo.f_str1(c1,N'%[a-zA-Z]%') FROM @test/*
ab
bc
cd
de
ef
*/
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
--中文过滤
declare @str varchar(max),@len int,@con int,@str1 varchar(max)
set @str='NAL-JH08-透明-G628-黑盖'
set @len=LEN(@str)
set @str1=''
set @con=1
while @con <=@len
begin
if datalength(substring(@str,@con,1))=1
begin
set @str1=@str1+substring(@str,@con,1)
set @con=@con+1
end
else
begin
break;
end
end
SET @str1=LEFT(@str1,LEN(@str1)-1)
print @str1/*----------------
NAL-JH08(1 行受影响)*/
--演示环境
CREATE TABLE TEST(NAME VARCHAR(100))
INSERT INTO TEST(NAME)
SELECT 'NAL-JH08-透明-G628-黑盖' UNION ALL
SELECT 'NAL-透明-G628-黑盖' UNION ALL
SELECT 'JH08-透明-G628-黑盖' UNION ALL
SELECT 'NAL-JH08-G628透明-G628-黑盖'----函数
CREATE FUNCTION GET_NEWSTR(@oldstr VARCHAR(100)) RETURNS VARCHAR(100)
AS
BEGIN
declare @len int,@con int,@newstr varchar(max)
set @len=LEN(@oldstr)
set @newstr=''
set @con=1
while @con <=@len
begin
if datalength(substring(@oldstr,@con,1))=1
begin
set @newstr=@newstr+substring(@oldstr,@con,1)
set @con=@con+1
end
else
begin
break;
end
end
SET @newstr=LEFT(@newstr,LEN(@newstr)-1)
return @newstr
END--查询
SELECT NAME =dbo.GET_NEWSTR(NAME)
FROM TEST /*
NAME
----------------------------------------------------------------------------------------------------
NAL-JH08
NAL
JH08
NAL-JH08-G62(4 行受影响)*/
--
DROP TABLE TEST
DROP FUNCTION dbo.GET_NEWSTR
--更新
UPDATE TEST SET NAME=dbo.GET_NEWSTR(NAME)
--
SELECT * FROM TEST/*
NAME
----------------------------------------------------------------------------------------------------
NAL-JH08
NAL
JH08
NAL-JH08-G62(4 行受影响)*/
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
--------------------------------------------------------------------
--过滤重复字符
IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR
GO
CREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
IF LEFT(@S,1)<>@SPLIT
SET @S = @SPLIT+@S
IF RIGHT(@S,1)<>@SPLIT
SET @S = @S+@SPLIT
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
BEGIN
SET @INDEX = CHARINDEX(@SPLIT,@S)
SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF @NEW IS NULL
SET @NEW = ISNULL(@NEW,'')+@TEMP
ELSE
SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
WHILE CHARINDEX(@TEMP,@S)>0
BEGIN
SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
END
END
RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',')
--A,B,C
GO
--------------------------------------------------------------------
--过滤重复字符2
IF OBJECT_ID('DBO.DISTINCT_STR2') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR2
GO
CREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(8000))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
WHILE LEN(@S)>0
BEGIN
SET @NEW=ISNULL(@NEW,'')+LEFT(@S,1)
SET @S=REPLACE(@S,LEFT(@S,1),'')
END
RETURN @NEW
END
GO
SELECT DBO.DISTINCT_STR2('AABCCD')
--ABCD
GO--------------------------------------------------------------------
IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL
DROP FUNCTION DBO.SPLIT_STR
GO
CREATE FUNCTION DBO.SPLIT_STR(
@S varchar(8000), --包含多个数据项的字符串
@INDEX int, --要获取的数据项的位置
@SPLIT varchar(10) --数据分隔符
)
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN int
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0
SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
END
GO
PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
--
GO
USE tempdbgo
CREATE FUNCTION fn_Str(
@Str nvarchar(1000)
)
RETURNS nvarchar(1000)
AS
begin
DECLARE @i INT,@s NVARCHAR(1000)
SELECT @i=PATINDEX('%[a-zA-Z]%',@Str),@s=''
IF @i=0
RETURN ''
WHILE @i>0
BEGIN
IF SUBSTRING(@str,@i,1) NOT LIKE '%[a-zA-Z0-9-]%'
BREAK
SELECT @s=@s+SUBSTRING(@str,@i,1)
SET @i=@i+1
END
WHILE RIGHT(@s,1)='-'
SELECT @s=LEFT(@s,LEN(@s)-1)
RETURN @s
end
go
DECLARE @str sysname
SET @str=N'NAL-JH08-透明-G628-黑盖'SELECT dbo.fn_Str(@str)/*
NAL-JH08
*/