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:[vbcol=seagreen]
> That kind of works, except if the column is a smallint I get 0 instead of
''
> "Robert Klemme" wrote:
>|||Try this.
SELECT ISNULL(CONVERT(VARCHAR(5),col_x), '') AS col_x FROM your_table
"Arne" wrote:
[vbcol=seagreen]
> That kind of works, except if the column is a smallint I get 0 instead of
''
>
> "Robert Klemme" wrote:
>