built in function

How is it possible to show null if the field is empty?

i.e.
select
ID,
name
from
table1

it should show something like:
1, 'jo'
2, NULL
3, NULL
4, 'Jack'
...

Thanks

use nullif(column,'')

Code Snippet

Create Table #data (

[Id] int ,

[Name] Varchar(100)

);

Insert Into #data Values('1','jo');

Insert Into #data Values('2','');

Insert Into #data Values('3',' ');

Insert Into #data Values('3','');

Insert Into #data Values('4','Jack');

Select Id, nullif(name,'') From #data

|||

Try:

Code Snippet

select

ID,

nullif(ltrim(rtrim(name)), '') as [name]

from

table1

-- or

select

ID,

case when ltrim(rtrim(name)) = '' then NULL else [name] end as [name]

from

table1

AMB

|||

Kent,

I am not sure how COALESCE function will fit for this issue.

NULLIF function is the ANSI standard and supported by most of the RDBMS databases.