Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Expanding Tab Characters - SQL Server

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