How do I make a column not nullable

This is what I would like to do...

1. Alter Table Status
Add ConsiderOpenFlag int null

2. UPDATE values...

3. Alter Table Status
Alter ConsiderOpenFlag int not null

Steps 1 and 2 are easy. What I cannot figure out is step three.

I don't want to have a default on that column, though I wouldn't mind adding it and then dropping it later if it would help.

Jonathan

The correct syntax is:

ALTER TABLE Status ALTER COLUMN ConsiderOpenFlag int not null

Best is probably to add the column as not null with default and then drop the default later. If there are large number of rows in the table then you may want to go with the UPDATE method and perform the UPDATE in batches to reduce the logging / locking resources.

sql

How do I exclude null fields?

Is there a way to write a select statement that will pull only fields that are not null?

SELECT col1, col2 FROM yourtable WHERE yourcol IS NOT NULL

col1, col2, and yourcol are columns in your table.

If you have specific question, please post it here.

|||Thanks!

How do I exclude carriage returns as part of a IS NULL exclusion clause...

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]

Cheers - AndyThis is a really slippery slope, but if you are dealing with 8 bit ASCII, you could use:LIKE '%[!-~]%' to see if there are any printable characters (this expression ignores whitespace).

-PatP|||Very interesting and very cool (speaking purely as a geek!).

I got it to work by doing this:

and field like '%[a-z0-9]%'

This expression only shows fields that contain letters and/or numbers and excludes stupid entries like periods, comma's, carraige returns...

Thanks very much for your help!

Andy|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'You are quite correct, systems that are derived from MS-DOS see 0x0d0a as a line end. The code that I proposed dodges that bullet altogether though, along with several others, and seems to have solved the problem better than I'd expected!

-PatP

How do i enter NULL in a table cell in the Enterprise Manager UI for table data entry?

I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.
In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.
The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).
How is that done?
It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.
Anybody know?
Thank you.
Tom

CTRL + 0 will do it.