oracle字符串只截取中文,Oracle截取中英文混合字符串的处理

if object_id('testfunc') is not null drop function testfunc

go

create function testfunc(@Title varchar(200), @len int) returns varchar(200)

as

begin

declare @s varchar(200)

set @s = @Title

if datalength(@Title) > @len

begin

declare @i int, @n int

set @i = 0

set @n = @len

while @i < @len - 1

begin

if ascii(@s) > 127

begin

set @i = @i + 1

set @n = @n - 1

end

set @i = @i + 1

set @s = right(@s, len(@s) - 1)

end

if @i = @len or ascii(@s) < 128

set @s = left(@Title, @n)

else

set @s = left(@Title, @n - 1)

end

return(@s)

end

go

select 1 as id, dbo.testfunc('12345678', 5) as title

union select 2, dbo.testfunc('1234五六', 5)

union select 3, dbo.testfunc('1二三四五六', 5)

union select 4, dbo.testfunc('一二3四五六', 5)

union select 5, dbo.testfunc('一二三四五六', 5)

union select 6, dbo.testfunc('一', 5)

/*

id      title

-------------

112345

21234

31二三

4一二3

5一二

6一

*/

drop function testfunc