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