Change NULL value in SQL SERVER

Navigation
3

How to change the NULL value in sql server for example 'NONE', how to leave this fixed?

My result is coming like this: 

Florida United States   NULL    16
Nevada  United States   NULL    17
Nevada  United States   Cell Phone  7
Nevada  United States   Home    2
Nevada  United States   Work    1
Roma    Italia  NULL    131
Roma    Italia  Cell Phone  28
Roma    Italia  Home    4
Roma    Italia  Work    3
Rosário Argentina   NULL    7
São Paulo   Brazil  NULL    7
Yucatán Mexico  NULL    8

I would like to be 'NONE' instead of NULL

    
asked by anonymous 19.09.2014 / 20:56

5 answers

2

I have found out how to use ISNULL, for example: 

ISNULL(cTelephoneType,'NONE')

So if cTelephoneType is NULL it will bring 'NONE'.

    
19.09.2014 / 21:11
3

Daniel, you can set the default value of field to NONE in the SQL Server table.

Thus, every field NULL will be NONE . 

ALTER TABLE SUA_TABELA ADD CONSTRAINT DF_NomeQualquer DEFAULT N'NONE' FOR SUA_COLUNA;
    
19.09.2014 / 21:13
3

You failed to quote COALESCE 

SELECT COALESCE(coluna, 'NONE') ...

This function is standard in the SQL language and applies to other banks (which implement it logically).

You can also use: 

SELECT COALESCE(coluna1, coluna2, coluna3, 'NONE') ...

So if column1 is null, get the value of column2 and so on.

    
13.10.2015 / 19:06
3

You have two options for making this change. The first would be to check on your Select if the value of your columnar is NULL as follows. 

Select ISNULL(States ,'NONE') as States from suatabela
  

ISNULL check if the field is null and changes to the value passed in the second parameter if true.

The second way would be to define the fields of your table with value DEFAULT , so you would have to do in two steps one would make a updade in the fields and then change the field with the value Default .

Using SQL Server Management Studio

  • In Object Explorer, right-click the table with the columns whose scale you want to change and click Design.
  • Select the column for which you want to specify the default value.
  • On the Column Properties tab, enter the new default value in the Default Value or Membership property.
    •   

    Note To enter a default numeric value, enter the number.   an object or function enter its name. For an alphanumeric pattern enter   the value in single quotation marks.

  • On the File menu, click Save table name
    • 27.11.2015 / 13:44
    1

    Hello, Daniel. The modification is simple, but I do not understand your motivation. Does NULL not meet your requirement? It is more consistent to have this data as NULL in the bank.

    Here's an example: 

    'update tabela set coluna = 'NONE' where coluna is NULL;'

    Correcting Pro Select: 

    'select t.a,
        t.b, 
        CASE WHEN t.c IS NULL
          THEN 'NONE'
          ELSE t.c END'
from tabela as t
        
    19.09.2014 / 21:02
    How to go to a part of a text on the same page by clicking on a link above Behavior of malloc (1) in C