Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ASpencer
Starting Member
3 Posts |
 Posted - 2012-07-14 : 10:16:40
|
| Hello,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 likeSCORE TableNULL600.000000NULLNULL350.000000This is the code i used to get rid of the trailing 0'sfloor(Score) ....which works fine but, how do I replace the NULL with a blank.Please Help. Thank You. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-14 : 10:20:46
|
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; |
 |
|
|
ASpencer
Starting Member
3 Posts |
Posted - 2012-07-14 : 10:38:21
|
| Thanks for responding quickly.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?Thank You. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-14 : 10:47:30
|
| [code]SELECT COALESCE(FLOOR(score),0) FROM YourTable;[/code]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. |
|
|
|
ASpencer
Starting Member
3 Posts |
Posted - 2012-07-14 : 10:54:15
|
| Thank you very much. It worked.Artnette |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-14 : 13:10:33
|
| You are very welcome. 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