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 
