remove chr(10) char with Tsql?

Hello,
I am writing data to a table using a DTS package from a VB
app. One of the columns, nvarchar column, is getting
carriage/return char appended to the data.
Select '*' + fld1 + '*' From tbl1
fld1 contains digits. I get this
* 10*
If I do this:
Declare @.s varchar(10)
Select @.s = fld1 From tbl1
Print '*' + @.s + '*'
I get this:
*
10*
If I say Update tbl1 Set fld1 = Ltrim(fld1)
I still get * 10*. But if I say Update tbl1 Set fld1 = 10
Now I get *10*
How can I remove these chr(10) chars with Tsql? Should I
loop through a cursor and use Substring?
declare @.s varchar(20)
declare @.t varchar(20)
select @.s = cn from tblincidentsn where rownum = 1
print '*' + @.s + '*'
set @.t = substring(@.s,3, len(@.s) - 2)
print ''
print '*' + @.t + '*'
gives me
*
02*
*02* <-- correct format
Thanks in advance for any suggestions.
RonAre you sure you don't want both CHAR(13) and CHAR(10) (CR and LF,
respectively)?
SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||SET @.s = REPLACE(REPLACE(@.s, CHAR(10), ''), CHAR(13), '')
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||> SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
I usually replace them separately, for two reasons:
(a) not all programs behave well. Some only include 10, some only include
13.
(b) not all programs behave well. Many include these in the wrong order.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePxgmo2IFHA.3484@.TK2MSFTNGP12.phx.gbl...
> (a) not all programs behave well. Some only include 10, some only include
> 13.
> (b) not all programs behave well. Many include these in the wrong order.
Both are excellent points!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--