Null valus in a select

How do I check of null in a select clause and replace them with space/empty
string?

SELECT COALESCE(col_x, '') AS col_x
FROM your_table ;

--
David Portas
SQL Server MVP
--

David Portas  wrote:
> SELECT COALESCE(col_x, '') AS col_x
>  FROM your_table ;

or

SELECT ISNULL(col_x, '') AS col_x
FROM your_table

robert

That kind of works, except if the column is a smallint I get 0 instead of ''

"David Portas" wrote:

> SELECT COALESCE(col_x, '') AS col_x
>  FROM your_table ;
>
> --
> David Portas
> SQL Server MVP
> --
>
>

That kind of works, except if the column is a smallint I get 0 instead of ''



"Robert Klemme" wrote:

> David Portas wrote: 
>
> or
>
> SELECT ISNULL(col_x, '') AS col_x
>  FROM your_table
>
>     robert
>
>

Arne,

An expression in the Selection List can only return the predefined data
type. If you want to return a smallint, then by definition, you cannot
return an empty string. If returning a string works for you, then you
can use this:

SELECT COALESCE(CAST(col_x AS VARCHAR(11)), '') AS col_x
FROM your_table

Gert-Jan


Arne  wrote:[color=darkred
]
>
> That kind of works, except if the column is a smallint I get 0 instead of 
''
>
> "Robert Klemme" wrote:
> 

Try this.

SELECT  ISNULL(CONVERT(VARCH
AR(5),col_x), '') AS col_x  FROM your_table

"Arne" wrote:

> That kind of works, except if the column is a smallint I get 0 instead of 
''
>
>
>
> "Robert Klemme" wrote:
>