IF data THEN text OR IF data following THEN this text ELSE (IF data NOT following) that text

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IF data THEN text OR IF data following THEN this text ELSE (IF data NOT following) that text

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-05 : 09:00:50

Angelo writes "Sorry for the 'cryptic' title...

The problem:

I have a DB listing books. All data are 'very well' broken down into their own fields, like 'pages', 'price', etc.

The need arises to re-connect few fields together, intermixed by puntuaction, like
title,(comma) author,(comma) place,(comma) date,(comma), edition.(full stop)

What will be the exact SQL to connect all the above together, keeping present eventual NULLs (for certain titles I may not have the data for 'edition', for ex.)

And also: if the last field IS NULL, the before-last field should change the following punctuation, i.e. should be followed by a full stop, no more by a comma.

In other words:
HOW TO CODE AN SQL TAKING CARE OF DYNAMIC DATA AND/OR DYNAMIC TEXT (text connected to the presence or absence of data)?

Hope you can see trough the fog of my confusion.
Thank you for your time.

(Win2K AdvServ + SQLServ2K)"

Jay99

468 Posts

Posted - 2002-03-05 : 09:30:32

One solution . . .


select
    case
        when edition is not null then 
            isnull(title,'') + ', ' +
            isnull(author,'') + ', ' +
            isnull(place,'') + ', ' +
            isnull(convert(varchar,date),'') + ', ' +
            edition + '.'
        else
            isnull(title,'') + ', ' +
            isnull(author,'') + ', ' +
            isnull(place,'') + ', ' +
            isnull(convert(varchar,date),'') + '.'
    end
from
    ....

Jay

Edited by - Jay99 on 03/05/2002 09:31:29

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources