Technical Article

Function to Expand Tab Characters

,

This is a function that turns tab characters into spaces. This is not as easy as one might think because of the way that tab characters work. In a non-proportionally spaced text layout, text is positioned according to the number of spaces, which are regarded as being the width of a column. A tab character moves the insertion point to the next tab position. Tab positions are every eighth column.

I suspect that there is an easier way of doing it!- however this routine seems to work and it is fast enough for my purposes.

ALTER FUNCTION dbo.[expandtabs]
 (
 @String VARCHAR(8000),
 @tabsize INT = NULL
 ) 
/*Returns a copy of string where all tab characters are expanded using spaces. 

SELECT dbo.expandTabs('this is a tab and here too and here
and a new line tab tab',null)
SELECT dbo.expandTabs(
'Begin
 insert into table
 select * from OtherTable
end',8)

SELECT '['+dbo.expandTabs('|'+char(09)+'|'+char(09)+'|'+char(09)+'|',8)+']'
*/RETURNS VARCHAR(8000)
AS BEGIN
 SELECT @tabsize = COALESCE(@tabsize, 4)
 IF @string IS NULL RETURN NULL
 DECLARE @OriginalString VARCHAR(8000),
     @DetabbifiedString VARCHAR(8000), @Column INT, @Newline INT
 SELECT @OriginalString = @String, @DeTabbifiedString = '', @NewLine = 1,
     @Column = 1
 WHILE PATINDEX('%[' + CHAR(9) + CHAR(10) + ']%', @OriginalString) > 0
     BEGIN--do we need to expand tabs?
     IF CHARINDEX(CHAR(9), @OriginalString + CHAR(9)) 
         > CHARINDEX(CHAR(10), @OriginalString + CHAR(10)) 
         BEGIN--we have to deal with a CR
         SELECT @NewLine = 1, @Column = 1,
             @DeTabbifiedString = @DeTabbifiedString 
                + SUBSTRING(@OriginalString, 1, CHARINDEX(CHAR(10), @OriginalString)),
             @OriginalString = STUFF(@OriginalString, 1, 
                                   CHARINDEX(CHAR(10),@OriginalString), '')
         END
     ELSE 
         BEGIN--de-tabbifying
         SELECT @Column = @column 
                          + CHARINDEX(CHAR(9),
                             @OriginalString + CHAR(9)) - 1,
                             @DeTabbifiedString = @DeTabbifiedString 
                             + SUBSTRING(@OriginalString, 1, CHARINDEX(CHAR(9), @OriginalString) - 1)
         SELECT @DeTabbifiedString = @DeTabbifiedString 
                    + SPACE(@TabSize - ( @column % @TabSize )),
                @OriginalString = STUFF(@OriginalString, 1, CHARINDEX(CHAR(09),@OriginalString), '')
         SELECT @Column = @Column + ( @TabSize - ( @column % @TabSize ) )
         END
     END
     RETURN @DeTabbifiedString + @Originalstring
 END
GO

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)