Database

<br />
---------------------------------------------------------------------------<br />
CREATE  FUNCTION F_FormatRevisionDescForPrint(@Desc VARCHAR(1000), @DisplayLenghOfString INT)<br />
RETURNS VARCHAR(2000)<br />
AS<br />
<br />
BEGIN<br />
 DECLARE<br />
   @CharacterCounter  INT<br />
  ,@DescLen   INT<br />
  ,@WhenToBreak   INT<br />
  ,@FormattedDesc   VARCHAR(2000)<br />
  ,@NewLine   VARCHAR(2)<br />
  ,@Space    VARCHAR(1)<br />
  ,@NextCharacter   VARCHAR(1)<br />
  ,@PreviousCharacter  VARCHAR(1)<br />
  ,@CurrentCharacter  VARCHAR(1)<br />
  ,@NewString   VARCHAR(2000)<br />
  ,@NumberOfCharactersToCut INT<br />
--<br />
SELECT   @CharacterCounter = 0<br />
 ,@DescLen     = LEN(@Desc)<br />
 ,@WhenToBreak      = 0<br />
 ,@FormattedDesc    = ''<br />
 ,@NewString    = ''<br />
 ,@NewLine     = CHAR(13) + CHAR(10)<br />
 ,@Space     = ' '<br />
--<br />
IF (@DescLen > 0) BEGIN<br />
 WHILE @CharacterCounter <= @DescLen BEGIN<br />
  --<br />
  SELECT   @FormattedDesc  = @FormattedDesc + SUBSTRING(@Desc,@CharacterCounter,1)<br />
   ,@CurrentCharacter = SUBSTRING(@Desc,@CharacterCounter,1)<br />
   ,@NextCharacter  = SUBSTRING(@Desc,(@CharacterCounter+1),1)<br />
   ,@PreviousCharacter  = SUBSTRING(@Desc,(@CharacterCounter-1),1)<br />
   ,@WhenToBreak   = @WhenToBreak + 1<br />
  --<br />
  IF (@WhenToBreak = @DisplayLenghOfString) BEGIN<br />
   -- I need to check and make sure that I am not breaking on<br />
   -- a word.<br />
   IF (@CurrentCharacter = @Space) BEGIN<br />
    -- There is a space here so it is ok to break<br />
    -- I know that I am not in the middle of a word<br />
    SELECT   @FormattedDesc  = @FormattedDesc + '<BAR />'<br />
     ,@WhenToBreak   = 0<br />
     ,@CharacterCounter  = @CharacterCounter + 1<br />
    -- <br />
   END ELSE IF (@CurrentCharacter != @Space) BEGIN<br />
    -- houston we have a problem I am somewhere in a word<br />
    -- I have to go back in the string till I find a space.<br />
    -- When I find a space I am going to remove the rest<br />
    -- of the characters from the string.<br />
    -- EX: String: A T<BR>OOL New String: A<BR> The word tool will be cut off.<br />
    -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX<br />
    -- reverse the string that way it will be<br />
    -- easier to find the space<br />
    SET @NewString    = REVERSE(@FormattedDesc)<br />
    -- get the first index of a space<br />
    SET @NumberOfCharactersToCut  = PATINDEX('% %',@NewString)-1<br />
    -- now reverse the string again so it is no longer backwords<br />
    SET @NewString    = REVERSE(@NewString)<br />
    --<br />
    -- get the new substring<br />
    SELECT   @FormattedDesc    = '' + SUBSTRING(@NewString,0,(LEN(@NewString)-@NumberOfCharactersToCut)) + '<BAR />'<br />
     ,@WhenToBreak      = 0<br />
     ,@CharacterCounter = @CharacterCounter-@NumberOfCharactersToCut<br />
   END<br />
<br />
  END ELSE BEGIN<br />
   --<br />
   SELECT @CharacterCounter = @CharacterCounter + 1<br />
   --<br />
  END<br />
 END<br />
END ELSE BEGIN<br />
 SET @FormattedDesc = ''<br />
END<br />
<br />
RETURN @FormattedDesc<br />
<br />
END<br />
-------------------------------------------------------------------------<br />

<br />
SELECT REPLACE(LTRIM(RTRIM(dbo.F_FormatRevisionDescForPrint(Routing_Revisions_Edit.[Description],47))),'<BAR />','<BR>') AS Description<br />
FROM MyTable<br />