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)
as
begin
declare @c int = 1, @space int = (LEN(@string)-LEN(replace(@string,' ','')))+1
declare @fragment varchar(20), @newstring varchar(100)
while @c <= @space
begin
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 += 1
end
return @newstring
end
select dbo.fn_TruncateWords ('Johnny found an apple on the ground below')
---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum
Abbreviate words
Posted - 01/05/2013 : 17:43:03
