declare @s varchar(100),@i int,@s2 varchar(100) set @s='111中国你好aa吗?a' set @i=0 set @s2='' while @i<=len(@s) begin if substring(@s,@i,1) not like '%[^吖-咗]%' set @s2=@s2+substring(@s,@i,1) set @i=@i+1 end select @s2
那这个就是你的意思了..create function dbo.f_GetChinese(@str nvarchar(20)) returns nvarchar(20) as begin declare @re nvarchar(20) declare @i int set @re='' set @i=PATINDEX('%[吖-做]%',@str) set @str=substring(@str,@i,20) set @i=PATINDEX('%[^吖-做]%',@str) set @re=left(@str,@i-1) return @re end goselect dbo.f_GetChinese('111中国aaa')drop function f_GetChinese
create function dbo.f_GetChinese(@str nvarchar(20)) returns nvarchar(20) as begin declare @re nvarchar(20) declare @i int set @re='' set @i=PATINDEX('%[吖-做]%',@str) set @str=substring(@str,@i,20) set @i=PATINDEX('%[^吖-做]%',@str) set @re=left(@str,@i-1) return @re end goselect dbo.f_GetChinese('111中国aaa你好吗?') select dbo.f_GetChinese('111中国aaa') drop function f_GetChinese/* -------------------- 中国(1 行受影响) -------------------- 中国(1 行受影响) */
create function get_china(@s varchar(50)) returns varchar(50) as begin declare @i int set @i=1 while patindex('%[^吖-咗]%',@s) > 0 and @i<=len(@s) begin if substring(@s,@i,1) not like '%[吖-咗]%' set @s=stuff(@s,@i,1,'') else set @i=@i+1 end return @s endselect dbo.get_china('哈ab你好cd呵呵,,哈')哈你好呵呵哈 我都给去了....
create function fn_Test(@source nvarchar(1024)) returns nvarchar(1024) as begin declare @destination nvarchar(1024), @index int, @flag int select @flag=0, @index=1 while @flag=0 or substring(@source, @index, 1) not like '[^吖-咗]' begin if substring(@source, @index, 1) not like '[^吖-咗]' select @destination=isnull(@destination,'')+substring(@source, @index, 1), @flag=1 set @index=@index+1 end return @destination end go declare @string varchar(32) set @string='111中国aaa一二三bbb'select dbo.fn_Test(@string)/* ---- 中国(1 row(s) affected)*/drop function dbo.fn_Test
--建立如下函数 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('[^吖-座]') set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '') else set @i = @i +1 return @oldstr end goselect result = dbo.getnewstr('111中国aaa') select result = dbo.getnewstr('111中国aaa广东eodsa深圳')drop function dbo.getnewstr/* result ---------------------------------------------------------------------------------------------------- 中国(所影响的行数为 1 行)result ---------------------------------------------------------------------------------------------------- 中国广东深圳(所影响的行数为 1 行) */
declare @s varchar(100),@i int,@s2 varchar(100) set @s='111中国aaa' set @i=1while @i<=len(@s) begin if ascII(substring(@s,@i,1)) between 133 and 223 set @i=@i+1 else begin set @s = stuff(@s,@i,1,'') set @i = 1 end
如果你的字串里无全角字符,同时结果中不排除其它双字节类型字符,比如日文,韩文.DECLARE @s VARCHAR(100),@s1 VARCHAR(100) SELECT @s='111中国aaa人test我***是啊kao',@s1='' WHILE LEN(@s)>0 SELECT @s1=CASE WHEN DATALENGTH(RIGHT(@s,1))=2 THEN RIGHT(@s,1) ELSE '' END + @s1,@s=LEFT(@s,LEN(@s)-1) SELECT @s1 /* 中国人我是啊 */
楼上通过patindex函数的方法,最好显式加排序规则,不同排序规则首尾汉字不同。
like('[^吖-座]')这个记着就能满足你要求了
简单问题整这么复杂,厉害substring left right
怎么没人回答? 我真的不懂,莫非是所有汉字ascii码。
declare @t varchar(8000) set @t='中华111人民aaa共%@#$#%#^##$#L>和>DMFDFDJL''国您dfdfd好' select dbo.fun_ascii(@t) /* --------- 中华人民共和国您好(所影响的行数为 1 行) */create function fun_ASCII (@col varchar(8000)) returns varchar(8000) AS begin declare @returnchar varchar(8000),@len int select @returnchar='',@len=1 while(@len<=len(@col)) begin if(ASCII(substring(@col,@len,1))>122) set @returnchar=@returnchar+substring(@col,@len,1) set @len=@len+1 end return @returnchar enddrop function fun_ASCII
----
中国(1 row(s) affected)
*/
set @string='111中国aaa'select substring(@string, 4, 2)
select right(left(@string,5),2)/*
----
中国(1 row(s) affected)
----
中国(1 row(s) affected)
*/
RIGHT(字符串, N) --取右边的N个字符
LEFT(字符串, N) --取左边的N个字符
set @s='111中国你好aa吗?a'
set @i=0
set @s2=''
while @i<=len(@s)
begin
if substring(@s,@i,1) not like '%[^吖-咗]%'
set @s2=@s2+substring(@s,@i,1)
set @i=@i+1
end
select @s2
returns nvarchar(20)
as
begin
declare @re nvarchar(20)
declare @i int
set @re=''
set @i=PATINDEX('%[吖-做]%',@str)
set @str=substring(@str,@i,20)
set @i=PATINDEX('%[^吖-做]%',@str)
set @re=left(@str,@i-1)
return @re
end
goselect dbo.f_GetChinese('111中国aaa')drop function f_GetChinese
returns nvarchar(20)
as
begin
declare @re nvarchar(20)
declare @i int
set @re=''
set @i=PATINDEX('%[吖-做]%',@str)
set @str=substring(@str,@i,20)
set @i=PATINDEX('%[^吖-做]%',@str)
set @re=left(@str,@i-1)
return @re
end
goselect dbo.f_GetChinese('111中国aaa你好吗?')
select dbo.f_GetChinese('111中国aaa')
drop function f_GetChinese/*
--------------------
中国(1 行受影响)
--------------------
中国(1 行受影响)
*/
returns varchar(50)
as
begin
declare @i int
set @i=1
while patindex('%[^吖-咗]%',@s) > 0 and @i<=len(@s)
begin
if substring(@s,@i,1) not like '%[吖-咗]%'
set @s=stuff(@s,@i,1,'')
else
set @i=@i+1
end
return @s
endselect dbo.get_china('哈ab你好cd呵呵,,哈')哈你好呵呵哈
我都给去了....
create function fn_Test(@source nvarchar(1024))
returns nvarchar(1024) as
begin
declare @destination nvarchar(1024), @index int, @flag int
select @flag=0, @index=1
while @flag=0 or substring(@source, @index, 1) not like '[^吖-咗]'
begin if substring(@source, @index, 1) not like '[^吖-咗]'
select @destination=isnull(@destination,'')+substring(@source, @index, 1), @flag=1
set @index=@index+1
end
return @destination
end
go
declare @string varchar(32)
set @string='111中国aaa一二三bbb'select dbo.fn_Test(@string)/*
----
中国(1 row(s) affected)*/drop function dbo.fn_Test
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('[^吖-座]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
goselect result = dbo.getnewstr('111中国aaa')
select result = dbo.getnewstr('111中国aaa广东eodsa深圳')drop function dbo.getnewstr/*
result
----------------------------------------------------------------------------------------------------
中国(所影响的行数为 1 行)result
----------------------------------------------------------------------------------------------------
中国广东深圳(所影响的行数为 1 行)
*/
set @s='111中国aaa'
set @i=1while @i<=len(@s)
begin
if ascII(substring(@s,@i,1)) between 133 and 223
set @i=@i+1
else
begin
set @s = stuff(@s,@i,1,'')
set @i = 1
end
end
select @s
/*
----------------------------------------------------------------------------------------------------
中国(所影响的行数为 1 行)
*/
SELECT @s='111中国aaa人test我***是啊kao',@s1=''
WHILE LEN(@s)>0
SELECT @s1=CASE WHEN DATALENGTH(RIGHT(@s,1))=2 THEN RIGHT(@s,1) ELSE '' END + @s1,@s=LEFT(@s,LEN(@s)-1)
SELECT @s1
/*
中国人我是啊
*/
left
right
我真的不懂,莫非是所有汉字ascii码。
set @t='中华111人民aaa共%@#$#%#^##$#L>和>DMFDFDJL''国您dfdfd好' select dbo.fun_ascii(@t)
/*
---------
中华人民共和国您好(所影响的行数为 1 行)
*/create function fun_ASCII
(@col varchar(8000))
returns varchar(8000)
AS
begin
declare @returnchar varchar(8000),@len int
select @returnchar='',@len=1 while(@len<=len(@col))
begin
if(ASCII(substring(@col,@len,1))>122)
set @returnchar=@returnchar+substring(@col,@len,1)
set @len=@len+1
end
return @returnchar
enddrop function fun_ASCII