Expand all TABs in a String to Spaces

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: Unknown

Date: 10 Jun 2020

Description

Just replaces all TABS with spaces. Alter the TAB size variable in the function to reflect the number of spaces required

Code

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 BEGINSELECT @tabsize = COALESCE(@tabsize, 4)IF @string IS NULL RETURN NULLDECLARE @OriginalString VARCHAR(8000), @DetabbifiedString VARCHAR(8000), @Column INT, @Newline INTSELECT @OriginalString = @String, @DeTabbifiedString = '', @NewLine = 1, @Column = 1WHILE PATINDEX('%[' + CHAR(9) + CHAR(10) + ']%', @OriginalString) > 0BEGIN--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), '')ENDELSEBEGIN--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 ) )ENDENDRETURN @DeTabbifiedString + @OriginalstringENDGO