From a performance point of view, the following code won't win anyone any awards, however needs must and it should do the job. As you said, the remit looks a bit mad but the following code will take any string (up to 100 characters), reduce each individual character fragment within that string to a maximum of three characters and also limit the final output to 40 characters.Its a RBAR approach, as opposed to set-based, so if anyone can improve on it, I would be interested to see their work.create function fn_TruncateWords (@string varchar(100))returns varchar(40)asbegindeclare @c int = 1, @space int = (LEN(@string)-LEN(replace(@string,' ','')))+1declare @fragment varchar(20), @newstring varchar(100)while @c <= @spacebegin set @fragment = case when SUBSTRING(@string,0,CHARINDEX(' ',@string,0)) <> '' then SUBSTRING(@string,0,CHARINDEX(' ',@string,0)) else @string end set @string = REPLACE(@string,SUBSTRING(@string,0,CHARINDEX(' ',@string,0))+' ','') set @newstring = isnull(@newstring,'')+left(@fragment,3)+' ' set @c += 1endreturn @newstringend
select dbo.fn_TruncateWords ('Johnny found an apple on the ground below')---------------------------------------------------------------------------------http://www.mannyroadend.co.uk A Bury FC supporters website and forum