I am new to MS SQL. I have a table that I am trying to remove decimals from the Score and NULLs in the same column. I know I can use the floor function to get rid of the decimal and trailing 0's. But how do I get rid of the NULL value and replace with a blank space?
For Example: this is what the table current look like
SCORE Table
NULL 600.000000 NULL NULL 350.000000
This is the code i used to get rid of the trailing 0's
floor(Score) ....which works fine but, how do I replace the NULL with a blank.
That depends on the data type of your column. If it is a numeric type - decimal, float etc. - you cannot put a blank (which is really an empty string) into it. You can set it to 0, or any other value, but that may not be what you want.
Now, if your column is a character type (and I hope it isn't) then you can replace NULLs with empty string using this:
UPDATE YourTable SET ColName = '' WHERE ColName IS NULL;
That score field type is (decimal(23,10), null).....how do I replace the NULL value with 0 and remove the decimal point & trailing 0's in one statement.
select floor(score) ....only removes the trailing zeros. How do I replace the NULL with 0 in the same select statement?
COALESCE (OR ISNULL) function picks the first value that is non-null from the parameters. So if FLOOR(SCORE) happens to be null, it will move on to the next parameter (0) and pick that.
As an aside, I assume you know that when you use floor function, it would chop off the fractional part - for example, 3.999 would get chopped to 3 rather than rounded to 4. If you want to round, use ROUND function, or CAST to decimal with scale 0.
NULL and Decimals in Same Column
Posted - 07/14/2012 : 10:16:40
