• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

how do I convert tab to spaces in an sql field

Hello:
I have the following function which I found in the internet and modified for my purposes, and I cannot make it to work.  I keep getting the same error-
"Invalid length parameter passed to the LEFT or SUBSTRING function."
I tried everything as shrinking the field size for the parameter and matching all variables to the same size, also changing the varchar for nvarchar, etc.
Please help!

ALTER FUNCTION [dbo].[ConvertTabsToSpaces] (@String varchar(7000))  
RETURNS varchar(7000) AS  
BEGIN
DECLARE
@start int,
@tempnote varchar(7000),
@temp char(1)


set @start=CHARINDEX(char(9), @String)
set @tempnote=substring(@String,1,(@start-1)) + ' '

While len(@String) > @start
begin
set @temp=substring(@String,@start,1)

IF @temp <> char(9)
begin
set @tempnote=@tempnote + @temp
end
else
begin
set @tempnote=@tempnote + ' '
end
SET @start=@start+1

END

RETURN(@tempnote)
end

thank you.
0
gloriagalvez
Asked:
gloriagalvez
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
ConvertTabsToSpaces

declare @tab as nvarchar(1)
set @tab = char(9)

return replace(@tempnote, @tab, ' ')
0
 
gloriagalvezAuthor Commented:
Hello:
Thank you very much.  Since the field had different kind of characters, I had to use the following:

 Replace(Replace(Replace(Replace(Replace(Replace(Replace('|' + FTRN_HISTORY.EXTENDED_DESCRIPTION + '|', char(9), ''), char(35), ''), char(10), ''), char(11), ''), char(12), ''), char(44), ''), char(13), '') AS 'DESCRIPTION'

Thank you again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now