dmcafee at pacbell.net
dmcafee at pacbell.net
Fri Mar 11 13:21:57 CST 2005
<back pedal mode on> WHERE LEN(Contact) = 0 would return a null when Contact is null, not a zero. the following below will take care of nulls, '', ' ', ' ' and variations of spaces UPDATE Projects2 SET ContactEmail = NULL WHERE ISNULL(LTRIM(RTRIM(@X)),'') = '' -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of dmcafee at pacbell.net Sent: Friday, March 11, 2005 10:14 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] Update query through Access ADP Steven since you have the answer to your question, I'd like to chime in to let you know that you can also do something like this: UPDATE Projects2 SET ContactEmail = NULL WHERE LEN(Contact) = 0 D -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Steve Erbach Sent: Friday, March 11, 2005 5:52 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Update query through Access ADP Dear Group, I've been looking through some of a client's ASP application with a view to making some slight modifications. (There are almost 800 ASP files. Yikes!) The back end is SQL Server and I've been using Access 2003 to construct views and stored procedures. I've been working on a COPY of the tables on my own SQL Server rather than the customer's live data. What's got me curious is this: I've been working with an sproc that updates a column containing an email address. It looks something like this: UPDATE Projects2 SET ContactEmail = NULL WHERE (Contact = '') OR (Contact = ' ') OR (Contact IS NULL) So if the Contact name doesn't have anything in it, I want the Contact's email address to be NULL. At present, there's a default e-mail address in every row. After the sproc runs I look at the table itself. Well, this table has 133,000 records in it. Access 2003 shows me the first 10,000 by default. But when I go past the 10,000th record, the e-mail column still has addresses in it even though the Contact field is empty. Is there something I'm missing in the sproc? Something like a command that says, "OK, I really, REALLY want to update the rows beyond 10,000, too." Regards, Steve Erbach Scientific Marketing Neenah, WI www.swerbach.com Security Page: www.swerbach.com/security _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com