How can I construct a person's name from five different columns?
HCP
I have four columns...
LAST Char(30)
FIRST Char(30)
MIDDLE Char(30)
TITLE Char(30)
SUFFIX Char(30)
The problem is that I want to format the name to look like this: "LAST,
TITLE FIRST MIDDLE SUFFIX". These fields are padded with spaces so I used
RTrim to write the query.
I would like to do this all in one select statement... this is what I
currently have...
SELECT UPPER(RTrim(Last) + ', ' + RTrim(Title) + ' ' + RTrim(FIRST) + ' ' +
RTrim(MIDDLE) + ' ' + RTrim(SUFFIX)) As Name
Now this works ok if all of the fields contain a value, but if one of the
fields are blank then I have superfluous spaces in newly created Name
column.
For example:
LAST = 'DOE '
FIRST = 'JOHN '
MIDDLE = 'A. '
TITLE = 'MR. '
SUFFIX = 'JR. '
Looks like this... "DOE, MR. JOHN A. JR." <-- That's good
But then:
LAST = 'DOE '
FIRST = 'JOHN '
MIDDLE = 'A. '
TITLE = ' '
SUFFIX = ' '
Looks like this... "DOE, JOHN A. " <-- That's not good because it has two
spaces instead of one between the "," and FIRST and an extra space at the
end. The only two fields which are going to be there every time are the
LAST and FIRST columns. I know I could use some LTrim statements in
addition to my RTrim statements, but isn't there some way I could do this
with IF statements or any other way?
If you couldn't tell I'm new to SQL Server programming. Any help is
appreciated!
Thanks!
-Paul
Tom Moreau
ISNULL (MyCol, '')
--
Tom
----------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
www.apress.com
---
HCP wrote in message ...
lindawie
Two ways of doing this. The CASE funciton, which I think is cleaner, and the
ISNULL() function. Either way we get the same result.
create table FullName (
LAST Char(30)not null,
FIRST Char(30) null,
MIDDLE Char(30) null,
TITLE Char(30) null,
SUFFIX Char(30) null,
)
insert Fullname values ('Winken', null, null, null, 'Jr')
insert Fullname values ('Blinken', 'A', 'B', 'Sir', null)
insert Fullname values ('Nod', null, null, 'Mr', 'Esq')
-- We only add the leading space if we have a value in the column.
SELECT UPPER(RTrim(Last) + ',' +
case when TITLE is null then '' else ' ' + rtrim(TITLE) end +
case when FIRST is null then '' else ' ' + rtrim(FIRST) end +
case when MIDDLE is null then '' else ' ' + rtrim(MIDDLE) end +
case when SUFFIX is null then '' else ' ' + rtrim(SUFFIX) end) As Name
from FullName
-- Note that we are concatenating a leading space to the column.
-- RTRIM will elimnate remove it if the column is null.
SELECT UPPER(RTrim(Last) + ',' + RTrim(' ' + isnull(Title, '')) + RTrim(' '
+ isnull(FIRST, '')) +
RTrim(' ' + isnull(MIDDLE, '')) + RTrim(' ' + isnull(SUFFIX, ''))) As Name
from FullName
Name
---------------------
WINKEN, JR
BLINKEN, SIR A B
NOD, MR ESQ
Name
---------------------
WINKEN, JR
BLINKEN, SIR A B
NOD, MR ESQ
Linda
"HCP" <pam...@yahoo.com> wrote in message
news:eijLAVsIBHA.1356@tkmsftngp02...
lindawie
> Pacl,
Sorry! :(
Linda
Ivan Arjentinski
I would generally prefer to use the COALESCE() function as it is ANSI
standard.
I also think that code would be cleaner with COALESCE() than CASE.
Third, a small suggestion, which might not be applicable:
Changing CHAR(30) to VARCHAR(30) would help avoid the repetitive use of
RTRIM() and could also save some space in the database pages and hence speed
up access.
--
Ivan Arjentinski
-----------------------------------------------------------
Checkout the SQL Upgrade Wizard -
upgrade production DB schema, while keeping the data.
www.aloeco.com/upgrader
-----------------------------------------------------------
"lindawie" <lind...@my-deja.com> wrote in message
news:#ReOpwsIBHA.1668@tkmsftngp03...
lindawie
> I would generally prefer to use the COALESCE() function as it is ANSI
> standard.
> I also think that code would be cleaner with COALESCE() than CASE.
I avoid coalesce completely. In this particular example you will get the
same query plan regardless of whether you use coalesce or isnull. However, I
have seen too many funky query plans that yield inefficient code with the
coalesce function. I don't know the reason or this, but it is so. I've never
had this problem using isnull. Performance is a high priority for me, so
that disqualifies coalesce. It is too much trouble to try to remember under
what set of circumstances it's safe to use coalesce and when I'd be shooting
myself in the foot with it. It's much easier to remember 'coalesce sucks'
and move on. :-)
I once conducted a quick Godden** Poll (ask the first six people through the
door) and it revealed that the majority of developers questioned could
neither give a dictionary definition of the word 'coalesce' nor could they
spell it correctly. If most people don't know what the word means, can't
even spell it, and are not familiar with the gotchas of the MS
implementation, what are the chances that they will understand and use the
function appropriately? Pretty slim, I'd guess. Isnull, on the other hand,
is a name that everyone seems to understand.
> Third, a small suggestion, which might not be applicable:
> Changing CHAR(30) to VARCHAR(30) would help avoid the repetitive use
> of RTRIM() and could also save some space in the database pages and
> hence speed up access.
Agreed, VARCHAR(30) is preferable for the reasons you state, but Paul's spec
calls for CHAR(30), so this is what we have to work with. Changing the data
type is always kinda iffy. You never know what you might break. There could
be reports that rely on the padding for correct formatting, or substring
function calls with the hardcoded value 30, or other such things.
** Godden Poll: Invented by Jean Godden, staff columnist with the Seattle
Times. She writes chatty, gossipy pieces with local interest. Vanity license
plates and her hairdresser's opinions figure prominently in her columns.
Linda
Ivan Arjentinski
> It's much easier to remember 'coalesce sucks'
> and move on. :-)
Please post examples where COALESCE leads to inefficient plan comparing to
ISNULL. This could be a bug in the optimizer, though I doubt.
COALESCE function is ANSI standard, which means that it makes the code more
portable and readable to more people.
--
Ivan Arjentinski
-----------------------------------------------------------
Checkout the SQL Upgrade Wizard -
upgrade production DB schema, while keeping the data.
www.aloeco.com/upgrader
-----------------------------------------------------------
"lindawie" <lind...@my-deja.com> wrote in message
news:#TFrQa1IBHA.1432@tkmsftngp03...
lindawie
Sometimes the coalesce function adds an extra outer join. Any code that has
a select statement as an arguument to coalesce is suspect. This occurs for
SQL Server 2000 and 7.0. If memory serves, this was also the case for 6.5,
but I don't feel like firing up my old computer to confirm that. It was
probably there the whole time. In any event, I noticed this behavior a long
time ago, so I stopped using coalesce. I always look at the execution plans
and IO statistics when writing code for production. Most people don't do
that, so I suppose that's why they haven't noticed this behavior.
> This could be a bug in the optimizer, though I doubt.
I doubt that this is a bug. I suspect it is just the nature of the function.
coalesce needs to be able to handle any number or arguments, isnull does
not.
> COALESCE function is ANSI standard, which means that it makes the code
more
> portable and readable to more people.
Writing ANSi-compliant code does not guarantee portability because different
vendors have implemented different feature sets. ANSI also has very little
to do with code readability. Things like consistent formatting, use of white
space, meaninful names, simple algorithms, etc have more of an impact on
readabilty. Just browse this newsgroup and you'll see plenty of
ANSI-compliant obfuscated code.
Just as a precaution to head off any pointless discussions: I'm not gonna
debate whether these queries are a 'good' way of doing things or not. Maybe
they are, maybe they aren't. That was not your question, so don't go there.
:-)
Linda
-- These two queries yield identical execution plans.
select authors.au_id, isnull(titles.price, 0)
from authors join titleauthor
on authors.au_id = titleauthor.au_id
join titles
on titles.title_id = titleauthor.title_id
select authors.au_id, coalesce(titles.price, 0)
from authors join titleauthor
on authors.au_id = titleauthor.au_id
join titles
on titles.title_id = titleauthor.title_id
-- These two yield different execution plans.
select authors.au_id, isnull((select titles.price from titles where
titles.title_id = titleauthor.title_id), 0)
from authors join titleauthor
on authors.au_id = titleauthor.au_id
select authors.au_id, coalesce((select titles.price from titles where
titles.title_id = titleauthor.title_id), 0)
from authors join titleauthor
on authors.au_id = titleauthor.au_id
-- Or if you like it in the WHERE clause ...
select authors.au_id
from authors
join titleauthor
on authors.au_id = titleauthor.au_id
where isnull((select titles.price from titles where titles.title_id =
titleauthor.title_id), 0) = 0
select authors.au_id
from authors
join titleauthor
on authors.au_id = titleauthor.au_id
where coalesce((select titles.price from titles where titles.title_id =
titleauthor.title_id), 0) = 0
/***
--using isnull()
Table 'titles'. Scan count 25, logical reads 50, physical reads 0,
read-ahead reads 0.
Table 'titleauthor'. Scan count 23, logical reads 23, physical reads 0,
read-ahead reads 0.
Table 'authors'. Scan count 1, logical reads 1, physical reads 0, read-ahead
reads 0.
-- using coalesce()
Table 'titles'. Scan count 41, logical reads 82, physical reads 0,
read-ahead reads 0.
Table 'titleauthor'. Scan count 23, logical reads 23, physical reads 0,
read-ahead reads 0.
Table 'authors'. Scan count 1, logical reads 1, physical reads 0, read-ahead
reads 0.
***/
"Ivan Arjentinski" <iva...@aloeco.com_> wrote in message
news:OLL3Xf#IBHA.1916@tkmsftngp04...
Ivan Arjentinski
> Sometimes the coalesce function adds an extra outer join.
Maybe this is because the optimizer recognizes COALESCE and uses special
techniques to make it run faster. Until repro proves opposite, this leads me
to the conclusion that COALESCE will run orders of magnitude faster than
ISNULL(). This would be because ISNULL() is not optimized and evaluated on
row-by-row bases. Well, have in mind, that this passage is based entirely on
your words, I didn't check-up anything regarding this...
> I doubt that this is a bug.
If the optimizer goes so far as to optimize COALESCE() with joins, but does
it (very) unsuccessfull, it will surely be a bug....
> ANSI also has very little
> to do with code readability.
Hmm. I doubt, but everyone is free to have opinion.
> Things like consistent formatting, use of white
> space, meaninful names, simple algorithms, etc have more of an impact on
> readabilty.
Totally, totally agree :))
Regards,
Ivan Arjentinski
-----------------------------------------------------------
Checkout the SQL Upgrade Wizard -
upgrade production DB schema, while keeping the data.
www.aloeco.com/upgrader
-----------------------------------------------------------
"lindawie" <lind...@my-deja.com> wrote in message
news:eOoNk3AJBHA.1340@tkmsftngp04...