REPLACE with NULLs ???

REPLACE with NULLs ???

Post by mike » Thu, 14 Dec 2000 07:05:38



Please help me with this serious but silly problem:

I have two SQL 7.0 Servers sharing a replicated database. This replication
was setup by my clients DBA - I am the ASP consultant.

Well, the same proc behaves differently on the two servers - specifically

works as expected by replacing the dash with NULL.

Thanks,

Mike

 
 
 

REPLACE with NULLs ???

Post by Tom Olso » Thu, 14 Dec 2000 08:27:12


There is a database option (sp_dboption), named 'concat null yields null'
are set the same on both servers.  Usually a string + NULL = NULL, at least
I believe this is the ANSI standard.

Tom


> ...
> Well, the same proc behaves differently on the two servers - specifically

> works as expected by replacing the dash with NULL.
> ...


 
 
 

1. Q: replacing spaces/null in char/varchar

All,

I have two fields, char(2) STATE ,  and varchar(15) CITY.

Anyway, I want to replace the values in either field when there is no data
inside. By "no data", I mean that the field will either contain
spaces or will be blank (but not null).

In access, I would do it as follows: iif(len(trim(nz(STATE))))=0,'XX',STATE)
as STATEABBREV. (It's a little hairy of a function, but it always works.)
I would do something similar for CITY.

Unfortunately, the isnull(STATE,'XX') function in T-SQL doesn't work the
same way, I think because there is a difference between
NULL and '' (blank). But how do I deal with this? I don't care if it's null,
blank, or spaces. It's all the same to me.

I suppose I could throw it into a function, but I don't want to do this for
each of the char/varchar fields I have in my 150 field table.

ANy suggestions?

Thanks,
-Mark

2. UPDATE performance problem

3. Replace <NULL> values

4. University Ingres Question??

5. UPDATE, Pro C, indicator variable to replace a Null in the table with data

6. OPTIMIZING - SELECT-SQL

7. using IF in sql statement (replace <null> w/ new value

8. Problem with compressed print

9. HELP!! Replace a NULL date with NOW()in SQL query.

10. selecting a smalldatetime field and replacing any nulls with "no date"

11. replace null with something else

12. How to replace NULL with 0 ?

13. Replace NULL in select query