SQL65, SQL2K, VARCHAR fields

SQL65, SQL2K, VARCHAR fields

Post by Paul » Sat, 01 Dec 2001 00:09:00



Hi all,

Here's what I'm trying to do:

    SELECT CASE WHEN RTRIM(col1) IS NOT NULL THEN col1
                            ELSE...
                    END
    FROM     sometable

Now this works just fine in SQL65. In SQL2K, RTRIM an emtpty string gets
you '', which is not NULL.
So, how can I perform this check in a way that will work in both
systems?
And I don't want to set my dbcmptlevel to 65.

Thanks,

Paul

 
 
 

SQL65, SQL2K, VARCHAR fields

Post by Zachary Well » Sat, 01 Dec 2001 00:36:13


I'm not sure what you're saying the problem is. RTRIM('') returns '' in my
SQL2K, which is what I expect to happen. Are you saying that in 6.5 it
returned NULL?

Zach


Quote:> Hi all,

> Here's what I'm trying to do:

>     SELECT CASE WHEN RTRIM(col1) IS NOT NULL THEN col1
>                             ELSE...
>                     END
>     FROM     sometable

> Now this works just fine in SQL65. In SQL2K, RTRIM an emtpty string gets
> you '', which is not NULL.
> So, how can I perform this check in a way that will work in both
> systems?
> And I don't want to set my dbcmptlevel to 65.

> Thanks,

> Paul


 
 
 

SQL65, SQL2K, VARCHAR fields

Post by Paul » Sat, 01 Dec 2001 01:17:29


Yup. You can see what the old behaviour was in BOL2K, sp_dbcmptlevel.
My problem is that my code is going to have to work in both systems for a few
weeks while we migrate.

Paul


> I'm not sure what you're saying the problem is. RTRIM('') returns '' in my
> SQL2K, which is what I expect to happen. Are you saying that in 6.5 it
> returned NULL?

> Zach



> > Hi all,

> > Here's what I'm trying to do:

> >     SELECT CASE WHEN RTRIM(col1) IS NOT NULL THEN col1
> >                             ELSE...
> >                     END
> >     FROM     sometable

> > Now this works just fine in SQL65. In SQL2K, RTRIM an emtpty string gets
> > you '', which is not NULL.
> > So, how can I perform this check in a way that will work in both
> > systems?
> > And I don't want to set my dbcmptlevel to 65.

> > Thanks,

> > Paul

 
 
 

SQL65, SQL2K, VARCHAR fields

Post by Zachary Well » Sat, 01 Dec 2001 01:42:06


I'm not 100% sure on how you want your logic to work but would something
like this work for you?

select
    case
        when len(rtrim(col1)) = 0 or rtrim(col1) is not null
                then col1
        else ...
    end

Zach


> Yup. You can see what the old behaviour was in BOL2K, sp_dbcmptlevel.
> My problem is that my code is going to have to work in both systems for a
few
> weeks while we migrate.

> Paul


> > I'm not sure what you're saying the problem is. RTRIM('') returns '' in
my
> > SQL2K, which is what I expect to happen. Are you saying that in 6.5 it
> > returned NULL?

> > Zach



> > > Hi all,

> > > Here's what I'm trying to do:

> > >     SELECT CASE WHEN RTRIM(col1) IS NOT NULL THEN col1
> > >                             ELSE...
> > >                     END
> > >     FROM     sometable

> > > Now this works just fine in SQL65. In SQL2K, RTRIM an emtpty string
gets
> > > you '', which is not NULL.
> > > So, how can I perform this check in a way that will work in both
> > > systems?
> > > And I don't want to set my dbcmptlevel to 65.

> > > Thanks,

> > > Paul

 
 
 

SQL65, SQL2K, VARCHAR fields

Post by Paul » Sat, 01 Dec 2001 01:55:20


Yeah, that's what I figured. I was hoping to do it without an OR, but I can't
see another way around it, etiher.
Thanks!

P


> I'm not 100% sure on how you want your logic to work but would something
> like this work for you?

> select
>     case
>         when len(rtrim(col1)) = 0 or rtrim(col1) is not null
>                 then col1
>         else ...
>     end

> Zach



> > Yup. You can see what the old behaviour was in BOL2K, sp_dbcmptlevel.
> > My problem is that my code is going to have to work in both systems for a
> few
> > weeks while we migrate.

> > Paul


> > > I'm not sure what you're saying the problem is. RTRIM('') returns '' in
> my
> > > SQL2K, which is what I expect to happen. Are you saying that in 6.5 it
> > > returned NULL?

> > > Zach



> > > > Hi all,

> > > > Here's what I'm trying to do:

> > > >     SELECT CASE WHEN RTRIM(col1) IS NOT NULL THEN col1
> > > >                             ELSE...
> > > >                     END
> > > >     FROM     sometable

> > > > Now this works just fine in SQL65. In SQL2K, RTRIM an emtpty string
> gets
> > > > you '', which is not NULL.
> > > > So, how can I perform this check in a way that will work in both
> > > > systems?
> > > > And I don't want to set my dbcmptlevel to 65.

> > > > Thanks,

> > > > Paul

 
 
 

1. PATINDEX in SQL65 vs. SQL2K

Hi all,

Can someone explain this please?

When I perform the following on SQL2K (no SP), I get the results shown
below the query.

EXEC sp_dbcmptlevel 'IMS_Publish', 65
EXEC sp_dbcmptlevel 'IMS_Publish'






GO

EXEC sp_dbcmptlevel 'IMS_Publish', 80
EXEC sp_dbcmptlevel 'IMS_Publish'






GO

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The current compatibility level is 65.

--------------------
%[- ,+_/\.();:"']%

(1 row(s) affected)

-----------
0

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The current compatibility level is 80.

--------------------
%[- ,+_/\.();:"']%

(1 row(s) affected)

-----------
0

(1 row(s) affected)

But, when I run the same query in SQL6.5 (SP post-5a) without the
sp_dbcmptlevel,  I get the expected answer:

--------------------
%[- ,+_/\.();:"']%

-----------
5

Why?

Thanks,

Paul

2. US-NJ-ORACLE HRMS 11I OAB FAST FORMULA

3. change sort order of sql2k moved from sql65?

4. Display record in the msflexgrid !!

5. Slow perf SQL65-> SQL2K

6. Accessing SQL Server database using IIS Anonymous Account, does this process

7. A Varchar(1000) field vs 1000 Bit fields

8. Accessing original TupleDesc from SRF]

9. Copying data from a TEXT field into two Varchar fields

10. Field Lenght Limitation in SQL 7 varchar field type

11. Using a text field to combine multiple varchar(255) fields

12. Blank field not space in char and varchar field

13. SQL 6.5 - text field to varchar(255) fields