declare @i int set @i =30 while @i>0 begin update 表 set 标题=left(标题,@i) where datalength(left(标题,@i))<=30 and datalength(标题)>30 set @i=@i-1 end
减少循环
declare @i int set @i =30 while @i>0 and exists (select 1 from 表 where datalength(标题)>30) begin update 表 set 标题=left(标题,@i) where datalength(left(标题,@i))<=30 and datalength(标题)>30 set @i=@i-1 end
一样,导到临时表,更新好了再显示;注意效率问题,毕竟用函数判断效率不会好
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