I can't quite remember writing this. I think I must have been trying to check out the process for submitting scripts for some reason, and grabbed something I had to hand. Apologies for the awful header, which explains very little. I hurriedly tried it out and to my surprise it seems to work.

The code comes from the Code Prettifier, of course, so if I extracted it properly, then I guess it works. I've updated the Prettifier since then but not this logic as it seems to work OK. Unfortunately, I haven't updated the version of the prettifier on http://extras.sqlservercentral.com/prettifier/default.aspx but I will as soon as I get a quiet week (nervous laugh)

ALTER FUNCTION [dbo].[fnConvertTabToSpaces]
    (
     @String VARCHAR(8000)
    ,@NumSpaces INT 
    )
RETURNS VARCHAR(8000)
AS 
    BEGIN
	-- Declare the return variable here
        DECLARE
            @Result VARCHAR(8000)
           ,@tab CHAR(1) = CHAR(09)

        SELECT
            @Result = REPLACE(@String, @tab, SPACE(@NumSpaces))

        RETURN @Result

    END