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