Data is there BUT could NOT find it!

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
--SeequellI'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
<Seequell@.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|||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
> <Seequell@.discussions.microsoft.com> 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
--***--
--Seequell
"Seequell" wrote:
> Hi Roy,
> I will test it soon and post the result. Thanks for replying.
> --
> --Seequell
>
> "Roy Harvey" 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