Change NULL value in SQL SERVER

Asked

Viewed 15,132 times

5

How to change the NULL value in sql server to for example 'NONE', how to fix it ?

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 wish it was 'NONE' instead of NULL

sql sql-server

5 answers

4

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

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

ISNULL check that 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, for that you would have to do in two steps one would be to do one updade fields and then changes the field to the value Default.

Using SQL Server Management Studio

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

Note To enter a standard numeric value, enter the number. To an object or function enter its name. For an alphanumeric pattern enter the value between single quotes.

  1. In the File menu, click Save table name

4


I found out how already...just use ISNULL , for example:

ISNULL(cTelephoneType,'NONE')

Ai if cTelephoneType for NULL will bring 'NONE'.

4

Daniel, can you define the default value from the field to NONE in the SQL Server table.

So every field NULL will be NONE.

ALTER TABLE SUA_TABELA ADD CONSTRAINT DF_NomeQualquer DEFAULT N'NONE' FOR SUA_COLUNA;

4

Failed to quote the COALESCE

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

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

It is also possible to use:

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

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

2

Hey there, Daniel. The modification is simple, but I don’t understand its motivation. The NULL doesn’t meet your requirement? It’s more consistent to have these data as NULL at the bank.

Follow 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

  • So but have to bring as NONE in my SELECT understood ? straight without I have to give an update

  • Do you just want to replace in select? Now it makes more sense. You could have posted select. select t.a,
 t.b, 
 CASE WHEN t.c IS NULL
 THEN 'NONE'
 ELSE t.c END from table as t

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.