>Here is the situvation. I can get BO_ID Field Value based on ROW_ID but I
>could not get the same record if I give the BO_ID Value. Please hava a look
>at the following query and other details.
>
>Can someone explain why and how it happens? Is it due to QUOTED_IDENTIFIER
>ON/OFF Setting. Let me know should you need more info. Thanks.
>
>USE ZADMIN
>
>---BO_ID is there.
>SELECT BO_ID, ROW_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
>
>   --BO_ID                                                ROW_ID
>   --898509508329674401118020031483   576
>
>   --Column_name       Type
>   --BO_ID                       varchar
>   --ROW_ID       int
>
>---But, could NOT find it if BO_ID is given.
>SELECT * FROM ZADMIN..CB_INST_IMPORT
>WHERE BO_ID = '898509508329674401118020031483'
>
>SELECT * FROM ZADMIN..CB_INST_IMPORT
>WHERE RTRIM(LTRIM(BO_ID)) = '898509508329674401118020031483'
>
>---Length of BO_ID is 30 but query says as 32.
>SELECT LEN(BO_ID) FROM ZADMIN..CB_INST_IMPORT
>WHERE ROW_ID = 576     --32
>
>--Seequell

"Roy Harvey" wrote:

> I'm curious about those two phantom characters.  Try running something
> like:
>
> SELECT *,
>        ASCII(SUBSTRING(BO_ID),01,1)),
>        ASCII(SUBSTRING(BO_ID),02,1)),
>        ASCII(SUBSTRING(BO_ID),03,1)),
>        ASCII(SUBSTRING(BO_ID),04,1)),
>        ASCII(SUBSTRING(BO_ID),05,1)),
> .....
>        ASCII(SUBSTRING(BO_ID),29,1)),
>        ASCII(SUBSTRING(BO_ID),30,1)),
>        ASCII(SUBSTRING(BO_ID),31,1)),
>        ASCII(SUBSTRING(BO_ID),32,1))
> FROM ZADMIN..CB_INST_IMPORT
> WHERE ROW_ID = 576
>
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 29 Jun 2006 15:05:01 -0700, Seequell
> <Seequ***@discussions.microsoft.com> wrote:
>
> >Here is the situvation. I can get BO_ID Field Value based on ROW_ID but I
> >could not get the same record if I give the BO_ID Value. Please hava a look
> >at the following query and other details.
> >
> >Can someone explain why and how it happens? Is it due to QUOTED_IDENTIFIER
> >ON/OFF Setting. Let me know should you need more info. Thanks.
> >
> >USE ZADMIN
> >
> >---BO_ID is there.
> >SELECT BO_ID, ROW_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> >
> >   --BO_ID                                                ROW_ID
> >   --898509508329674401118020031483   576
> >
> >   --Column_name       Type
> >   --BO_ID                       varchar
> >   --ROW_ID       int
> >
> >---But, could NOT find it if BO_ID is given.
> >SELECT * FROM ZADMIN..CB_INST_IMPORT
> >WHERE BO_ID = '898509508329674401118020031483'
> >
> >SELECT * FROM ZADMIN..CB_INST_IMPORT
> >WHERE RTRIM(LTRIM(BO_ID)) = '898509508329674401118020031483'
> >
> >---Length of BO_ID is 30 but query says as 32.
> >SELECT LEN(BO_ID) FROM ZADMIN..CB_INST_IMPORT
> >WHERE ROW_ID = 576     --32
> >
> >--Seequell
>

"Seequell" wrote:

> Hi Roy,
>
> I will test it soon and post the result. Thanks for replying.
> --
> --Seequell
>
>
> "Roy Harvey" wrote:
>
> > I'm curious about those two phantom characters.  Try running something
> > like:
> >
> > SELECT *,
> >        ASCII(SUBSTRING(BO_ID),01,1)),
> >        ASCII(SUBSTRING(BO_ID),02,1)),
> >        ASCII(SUBSTRING(BO_ID),03,1)),
> >        ASCII(SUBSTRING(BO_ID),04,1)),
> >        ASCII(SUBSTRING(BO_ID),05,1)),
> > .....
> >        ASCII(SUBSTRING(BO_ID),29,1)),
> >        ASCII(SUBSTRING(BO_ID),30,1)),
> >        ASCII(SUBSTRING(BO_ID),31,1)),
> >        ASCII(SUBSTRING(BO_ID),32,1))
> > FROM ZADMIN..CB_INST_IMPORT
> > WHERE ROW_ID = 576
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Thu, 29 Jun 2006 15:05:01 -0700, Seequell
> > <Seequ***@discussions.microsoft.com> wrote:
> >
> > >Here is the situvation. I can get BO_ID Field Value based on ROW_ID but I
> > >could not get the same record if I give the BO_ID Value. Please hava a look
> > >at the following query and other details.
> > >
> > >Can someone explain why and how it happens? Is it due to QUOTED_IDENTIFIER
> > >ON/OFF Setting. Let me know should you need more info. Thanks.
> > >
> > >USE ZADMIN
> > >
> > >---BO_ID is there.
> > >SELECT BO_ID, ROW_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> > >
> > >   --BO_ID                                                ROW_ID
> > >   --898509508329674401118020031483   576
> > >
> > >   --Column_name       Type
> > >   --BO_ID                       varchar
> > >   --ROW_ID       int
> > >
> > >---But, could NOT find it if BO_ID is given.
> > >SELECT * FROM ZADMIN..CB_INST_IMPORT
> > >WHERE BO_ID = '898509508329674401118020031483'
> > >
> > >SELECT * FROM ZADMIN..CB_INST_IMPORT
> > >WHERE RTRIM(LTRIM(BO_ID)) = '898509508329674401118020031483'
> > >
> > >---Length of BO_ID is 30 but query says as 32.
> > >SELECT LEN(BO_ID) FROM ZADMIN..CB_INST_IMPORT
> > >WHERE ROW_ID = 576     --32
> > >
> > >--Seequell
> >

> Found it. Hope I am right.
> Characters were Line feed (char(10)) and Carriage return (char(13)).
> Have a look at the query and result. Now, how do I remove it?
> Thanks a lot.
>
> SELECT BO_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
>
> BO_ID
> --------------------------------------------------
> 898509508329674401118020031483
>
> ---***---
>
> SELECT ASCII(SUBSTRING(BO_ID,01,1)),
>       ASCII(SUBSTRING(BO_ID,02,1)),
>       ASCII(SUBSTRING(BO_ID,03,1))
>    FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
>
>               
> ----------- ----------- -----------
> 56          57          56
>
> ---***---
>
> SELECT ASCII(SUBSTRING(BO_ID,29,1)),
>       ASCII(SUBSTRING(BO_ID,30,1)),
>       ASCII(SUBSTRING(BO_ID,31,1)),
>       ASCII(SUBSTRING(BO_ID,32,1))
>    FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576   
>                                  
> ----------- ----------- ----------- -----------
> 56          51          13          10
>
> ---***---
>

"Tracy McKibben" wrote:

> Seequell wrote:
> > Found it. Hope I am right.
> > Characters were Line feed (char(10)) and Carriage return (char(13)).
> > Have a look at the query and result. Now, how do I remove it?
> > Thanks a lot.
> >
> > SELECT BO_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> >
> > BO_ID
> > --------------------------------------------------
> > 898509508329674401118020031483
> >
> > ---***---
> >
> > SELECT ASCII(SUBSTRING(BO_ID,01,1)),
> >       ASCII(SUBSTRING(BO_ID,02,1)),
> >       ASCII(SUBSTRING(BO_ID,03,1))
> >    FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> >
> >               
> > ----------- ----------- -----------
> > 56          57          56
> >
> > ---***---
> >
> > SELECT ASCII(SUBSTRING(BO_ID,29,1)),
> >       ASCII(SUBSTRING(BO_ID,30,1)),
> >       ASCII(SUBSTRING(BO_ID,31,1)),
> >       ASCII(SUBSTRING(BO_ID,32,1))
> >    FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576   
> >                                  
> > ----------- ----------- ----------- -----------
> > 56          51          13          10
> >
> > ---***---
> >
>
> UPDATE CB_INST_IMPORT
> SET BO_ID = REPLACE(BO_ID, CHAR(13)+CHAR(10), '')
> WHERE ROW_ID = 576
>