Function to expand tab characters

Question

Function to expand tab characters

Phil Factor

SSC-Insane

Points: 20244
More actions
October 14, 2007 at 4:05 am

#375895

Comments posted to this topic are about the item Function to expand tab characters
Best wishes,
Phil Factor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Factor SSC-Insane Points: 20244 More actions · Answer 1

[p]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.[/p]

[font="Courier New"]DECLARE @TabbedStuff VARCHAR(2000)

SELECT @TabbedStuff='

'+CHAR(9)+ 'SELECT @tabsize = COALESCE(@tabsize, 4)

'+CHAR(9)+ 'IF @string IS NULL RETURN NULL

'+CHAR(9)+ 'DECLARE @OriginalString VARCHAR(8000),

'+CHAR(9)+CHAR(9)+'@DetabbifiedString VARCHAR(8000), @Column INT, @Newline INT

'+CHAR(9)+ 'SELECT @OriginalString = @String, @DeTabbifiedString = '', @NewLine = 1,

'+CHAR(9)+CHAR(9)+'@Column = 1

'+CHAR(9)+'WHILE PATINDEX(''%['' + CHAR(9) + CHAR(10) + '']%'', @OriginalString) > 0'

SELECT dbo.expandTabs(@TabbedStuff,3)

[/font][p]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)[/p]

Best wishes,
Phil Factor

William Soranno SSCommitted Points: 1713 More actions · Answer 2

Phil,

Here is a much simpler function.

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

Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

Great, but it does a much simpler operation and certainly doesn't de-tabbify. In code, you are likely to get a mixture of spaces and tabs. The tab character actually is a control character that, in a mono-spaced font, means 'take me to the next TAB setting. It does not mean 'I'm eight spaces'(or whatever). TABS may be set to every eighth character position (or whatever) and so your code will work until the point that it finds a mixture of spaces, tabs and other control characters. There is a subtle difference as you'll find out when you try your routine on code that has both tabs and spaces in it. Why does code have both tabs and spaces? Hell, the coder uses both, and just whacks either the space-bar or the TAB key until columns line up. Programmers get irritated if their columns stop lining up, which is what will happen if they use your code.

Best wishes,
Phil Factor