Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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