rename NULL column outputs

I have this view:
CREATE VIEW CautionDescriptions AS
SELECT TOP 100 PERCENT
Oils.OilName AS [Oil name], Cautions.Description AS Caution
FROM Cautions
INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
ORDER BY Oils.OilName
I want to return all rows even those with NULLs in the description key. Is
there a way to replace the <NULL>s in the description column with more
attractive text?
like
if OilCautions.OilID ISNULL 'No description available'
endif
Where could I put this in the SELECT or FROM clause?SELECT COALESCE(column_name, 'No description available')
You can also use ISNULL. Funny, I just wrote an article about this earlier
today:
http://www.aspfaq.com/2532
http://www.aspfaq.com/
(Reverse address to reply.)
"Patrick" <psully@.nospam.eatel.net> wrote in message
news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
> I have this view:
> CREATE VIEW CautionDescriptions AS
> SELECT TOP 100 PERCENT
> Oils.OilName AS [Oil name], Cautions.Description AS Caution
> FROM Cautions
> INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
> RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
> ORDER BY Oils.OilName
> I want to return all rows even those with NULLs in the description key. Is
> there a way to replace the <NULL>s in the description column with more
> attractive text?
> like
> if OilCautions.OilID ISNULL 'No description available'
> endif
> Where could I put this in the SELECT or FROM clause?
>
>|||Thanks Aaron! That was a fast answer. I never heard of coalesce. I am going
to read your article too, thanks again!
BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
Programming. The book name is certainly a misnomer, the book is mostly about
using Enterprise Manager.
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||Read your article. **** rating!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||> BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
> Programming. The book name is certainly a misnomer, the book is mostly
about
> using Enterprise Manager.
Then you might want to read this article too:
http://www.aspfaq.com/2455
:-)
PS I suggest creating your views from scratch, as opposed to letting
Enterprise Manager write the code for you ... because of the limitations in
the view designer, as well as its funky quirks like putting TOP 100 PERCENT
and ORDER BY in views for some reason.|||Oops, I think I hit reply too soon (may be an empty reply here soon).
Anyway, that was an interesting and informative article. I've taken a few
SQL Server courses, and they all start of with EM. But Step by Step does
cover the QA, too. I remember vaguely when I learned how to use it a little
last year, I was impressed a lot by QA. I mainy use EM for experimenting
with joins really fast, and if it works, copy the script and edit it in QA.
When are you going to turn all these "tips" into a book? lol, thanks!
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ur6ou3YAFHA.4072@.TK2MSFTNGP10.phx.gbl...
2000
> about
> Then you might want to read this article too:
> http://www.aspfaq.com/2455
> :-)
> PS I suggest creating your views from scratch, as opposed to letting
> Enterprise Manager write the code for you ... because of the limitations
in
> the view designer, as well as its funky quirks like putting TOP 100
PERCENT
> and ORDER BY in views for some reason.
>

removing chr(13) and chr(10) from SQL

Hi.

Is there a way to remove carriage returns and line feeds from a field in SQL.

Here is what I have at the moment:

CAST(m.Description AS VARCHAR(8000))AS "Product Description"

(have had to cast as VarChar because of problems with aggregate functions in rest of query).

Problem is that this m.description field has carriage returns and line breaks in it .. how do I remove these inside the SQL statement?

thanksHave you tried REPLACE ( StringExpression1 , StringExpression2 , StringExpression3 ) T-SQL function?|||I tried something like this:

CAST(replace(m.Description, vlbf, ' ') AS VARCHAR(8000))AS "Product Description",

and tried variations of vlbf such as chr(10) chr(13) or \r\n and none of them worked.

I can't put them in single quotes as SQL will look for the actual occurence in the string rather than what it stands for.

I get the error column vlbf is unknown or chr(10) is an unknown function...|||sorted it now.... used this in the end:

REPLACE(CAST(m.Description AS varChar(1000)),char(13) + char(10),' ') AS "Product Description",

Thanks for the help