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
end select dbo.get_china('但是111中国aaa人FDGtest我***是GFD啊kao多多うェ') as 中文 drop function dbo.get_china
这是个取中文的函数,要分别取英文大小写的话怎么改?最好能加上标点分隔开
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
end select dbo.get_china('但是111中国aaa人FDGtest我***是GFD啊kao多多うェ') as 中文 drop function dbo.get_china
这是个取中文的函数,要分别取英文大小写的话怎么改?最好能加上标点分隔开
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
end
go
create function get_china1(@s varchar(50))
returns varchar(50)
as
begin
declare @i int
set @i=1
while patindex('%[^A-Z]%',@s) > 0 and @i <=len(@s)
begin
if ascii(substring(@s,@i,1)) between 65 and 90
set @i=@i+1
else
set @s=stuff(@s,@i,1,'')
end
return @s
end
gocreate function get_china2(@s varchar(50))
returns varchar(50)
as
begin
declare @i int
set @i=1
while patindex('%[^A-Z]%',@s) > 0 and @i <=len(@s)
begin
if ascii(substring(@s,@i,1)) between 97 and 122
set @i=@i+1
else
set @s=stuff(@s,@i,1,'')
end
return @s
end
go
select dbo.get_china('但是111中国aaa人FDGtest我***是GFD啊kao多多うェ') as 中文
select dbo.get_china1('但是111中国aaa人FDGtest我***是GFD啊kao多多うェ') as [A-Z]
select dbo.get_china2('但是111中国aaa人FDGtest我***是GFD啊kao多多うェ') as [a-z]
drop function get_china,get_china1,get_china2/*
中文
--------------------------------------------------
但是中国人我是啊多多(所影响的行数为 1 行)A-Z
--------------------------------------------------
FDGGFD(所影响的行数为 1 行)a-z
--------------------------------------------------
aaatestkao(所影响的行数为 1 行)
*/
returns varchar(50)
as
begin
declare @i int
DECLARE @t varchar(20),@ss varchar(50)
set @i=1
SET @ss=''
SET @t=CASE @flag WHEN 0 THEN '[吖-咗]' WHEN 1 THEN '[a-z]' WHEN 2 THEN '[A-Z]' END
IF @flag=0
while patindex('%'+@t+'%',@s) > 0
begin
SET @ss=@ss+substring(@s,patindex('%'+@t+'%',@s),1)
SET @s=RIGHT(@s,len(@s)-patindex('%'+@t+'%' ,@s))
end
else
while patindex('%'+@t+'%' COLLATE chinese_prc_bin ,@s) > 0
begin
SET @ss=@ss+substring(@s,patindex('%'+@t+'%' COLLATE chinese_prc_bin,@s),1)
SET @s=RIGHT(@s,len(@s)-patindex('%'+@t+'%' COLLATE chinese_prc_bin ,@s))
end
return @ss
end
go
select dbo.get_china('但是111中国aaa人FDGtest我***是GFD啊kao多多うェ',0) as 中文
select dbo.get_china('但是111中国aaa人FDGtest我***是GFD啊kao多多うェ',1) as 小写
select dbo.get_china('但是111中国aaa人FDGtest我***是GFD啊kao多多うェ',2) as 大写
--result
/*
中文
--------------------------------------------------
但是中国人我是啊多多(所影响的行数为 1 行)小写
--------------------------------------------------
aaatestkao(所影响的行数为 1 行)大写
--------------------------------------------------
FDGGFD(所影响的行数为 1 行)
*/