extra space in stored proc generated table

I have the proc below.  The data portion of the returned table has an extra blank on the end of then data, except for the last row.   I need to get rid of this blank.  Even though I've added ltrim and rtrim, I'm still getting it.  How can I get rid of it?

CREATE FUNCTION [dbo].[Split_PValues]
(
      @RowData nvarchar(2000)
)  
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Data varchar(100)
)
AS  
BEGIN
      Declare @Cnt int
      Set @Cnt = 1
      Declare @delimiter nvarchar(7)
      Set @delimiter = 'Annual:'

      While (Charindex(@delimiter,@RowData)>0)
      Begin
            IF @Cnt > 1      Insert Into @RtnValue (data)
            
            Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@delimiter, @RowData)-1)))
            
            Set @RowData = Substring(@RowData,Charindex(@delimiter, @RowData) + len(@delimiter),len(@RowData))
            Set @RowData = ltrim(rtrim(@RowData))                        
                                                
            Set @Cnt = @Cnt + 1
      End
            
      Insert Into @RtnValue (data)
      Select Data = ltrim(rtrim(@RowData))

      Return
END
LVL 1
HLRosenbergerAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
I need to get rid of this blank.  Even though I've added ltrim and rtrim, I'm still getting it.  How can I get rid of it?
That would be because it is not a blank, but rather some other character.  If I was to bet I would suspect it is a Cr as in CHAR(13).  If you cannot do away with it, you can do something like this:

Insert Into @RtnValue (data)
Select Data = ltrim(rreplace(@RowData, CHAR(13), ''))
0
 
HLRosenbergerAuthor Commented:
Good idea.  I did not think of that.

Actually the last character is a 9.  So I did this:

Select Data = ltrim(rtrim(replace(@RowData, CHAR(9), ' ')))

but it did not seem to help.  I must be doing something wrong.
0
 
HLRosenbergerAuthor Commented:
Ah, I finally got it.

Set @RowData = Substring(@RowData,Charindex(@delimiter, @RowData) + len(@delimiter),len(@RowData))

Set @RowData = ltrim(rtrim(@RowData))

Set @RowData = replace(@RowData, CHAR(9), '')
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
HLRosenbergerAuthor Commented:
Thahnks!
0
 
Anthony PerkinsCommented:
Actually the last character is a 9.
That would be a Tab.  I would try and fix it in the source.
0
 
HLRosenbergerAuthor Commented:
The source is a commercial database.   I cannot fix it there
0
 
Anthony PerkinsCommented:
The source is a commercial database.   I cannot fix it there
I meant when you import it.  If you have no control over that, than I agree you will have to resort to some cheesy workaround.
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.

All Courses

From novice to tech pro — start learning today.