Cool Tips‎ > ‎Scripting SQL Server‎ > ‎

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 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





ċ
Andy Hughes,
Jun 10, 2020, 5:30 PM
Comments