HLRosenberger
asked on
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,@Row Data)>0)
Begin
IF @Cnt > 1 Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(Substring(@Row Data,1,Cha rindex(@de limiter, @RowData)-1)))
Set @RowData = Substring(@RowData,Charind ex(@delimi ter, @RowData) + len(@delimiter),len(@RowDa ta))
Set @RowData = ltrim(rtrim(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
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,@Row
Begin
IF @Cnt > 1 Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(Substring(@Row
Set @RowData = Substring(@RowData,Charind
Set @RowData = ltrim(rtrim(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah, I finally got it.
Set @RowData = Substring(@RowData,Charind ex(@delimi ter, @RowData) + len(@delimiter),len(@RowDa ta))
Set @RowData = ltrim(rtrim(@RowData))
Set @RowData = replace(@RowData, CHAR(9), '')
Set @RowData = Substring(@RowData,Charind
Set @RowData = ltrim(rtrim(@RowData))
Set @RowData = replace(@RowData, CHAR(9), '')
ASKER
Thahnks!
Actually the last character is a 9.
That would be a Tab. I would try and fix it in the source.
That would be a Tab. I would try and fix it in the source.
ASKER
The source is a commercial database. I cannot fix it there
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.
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.
ASKER
Actually the last character is a 9. So I did this:
Select Data = ltrim(rtrim(replace(@RowDa
but it did not seem to help. I must be doing something wrong.