SQL 2000 WTF???? | Tech Off

raptor3676

Can someone please explain this behavior?

I´ve executed the following sentence in Query Analyzer...

SELECT

DISTINCT PID_ID + '|',LEN(PID_ID)

FROM

AGFA_ADT_HIS

WHERE

(PID_ID LIKE '% ') --> there is an space after the % char

ORDER

BY 1

And I get results like this? (Using fixed-width font)

CC0              |, 3
CC03050635007948 |, 16
CC10017751       |, 10
CC100260         |, 8
CC10059647       |, 10

It doen't makes any sense since where part ask for those entries ending in space and according to the LEN function the fields doesn't have trailing spaces whatsover.

I think that the ANSI_PADDING has something to do with it but I'm not sure.

Thanx in advance!
Raptor

BTW: the column is VARCHAR(20)

Red5

he he.
There is some squiffy stuff that goes on with selecting varchars/chars in SQL Server 2000 I don't always fully understand either.

I had a similar thread awhile back that you can peruse.

stevo_

Isn't this purely the difference with char vs varchar, ie- char is a set length and will "padd" to fit that length if its not all taken up?

cokert

Where'd you get your data from? I had a problem in Oracle where a column was a char, but I needed varchar. Upon conversion, the varchars all ended in spaces. While you may not have the exact situation, you may have something similar.

I mocked up your situation and got no results from your query.

Here's the statements to get the test data...

create table temp ( pid_id varchar(20) )
insert into temp (pid_id) values ('CC0')
insert into temp (pid_id) values ('CC03050635007948')
insert into temp (pid_id) values ('CC10017751')
insert into temp (pid_id) values ('CC100260')
insert into temp (pid_id) values ('CC10059647')

and running this statement I get nothing
SELECT DISTINCT PID_ID + '|',LEN(PID_ID)
FROM temp
WHERE (PID_ID LIKE '% ') --> there is an space after the % char
ORDER BY 1

removing the where clause, I get this
--------------------- -----------
CC0|                  3
CC03050635007948|     16
CC10017751|           10
CC100260|             8
CC10059647|           10

cokert

ah. from the len documentation....
LEN

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

Check your data. You probably have the blanks really in the data.

I recreated my data via
insert into temp (pid_id) values ('CC0              ')
insert into temp (pid_id) values ('CC03050635007948 ')
insert into temp (pid_id) values ('CC10017751       ')
insert into temp (pid_id) values ('CC100260         ')
insert into temp (pid_id) values ('CC10059647       ')

and got identical results to your original post.

Matthew van Eerde

UPDATE
    AGFA_ADT_HIS
SET
    PID = RTRIM(PID)
WHERE
    PID LIKE '% '
    -- or better, LEN(PID) != DATALENGTH(PID) ?

raptor3676

Thanks all for the help.

Cokert wrote:

ah. from the len documentation....
LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

It made this mess have sense, and thanks to

Matthew van Eerde wrote:

UPDATE
    AGFA_ADT_HIS
SET
    PID = RTRIM(PID)
WHERE
    PID LIKE '% '
    -- or better, LEN(PID) != DATALENGTH(PID) ?

Now I have a workaround: The Update is out of the question since I'm not suppossed to change the date but the DATALENGTH function allows me to detect those records and treat them accordingly.

THANX