Re: Select Expression Operators
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/31/04
- Next message: KK: "Isn't Microsoft going to update English Query?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 31 Aug 2004 10:45:37 +0200
On Mon, 30 Aug 2004 22:49:06 -0700, Gary K wrote:
>Sorry for what must seem like a dumb question, but I was wondering why I keep
>getting "<operator> not recognized" errors when I try to use the following
>sql clause:
>
>SELECT personid, IIF(LEN(ISNULL(middlename, '')) = 0, firstname + ' ' +
>lastname, firstname + ' ' + middlename + ' ' + lastname) AS FullName FROM
>mydatabase
>
>Regardless of which operator I use (=, <, >, etc..) it is unrecognized when
>the statement gets compiled. All I want to do is use the sql query to
>concatenate the name strings to a full name without having extra spaces in
>the middle when there is no middle name specified. I was able to use this
>query with MS Access but I am having trouble porting it to SQL server. (Mind
>you, in access I was able to use ISNULL by itself, not just checking for zero
>length strings) Can someone please point out my error?
Hi Gary,
SQL Server doesn't offer the same set of functions as Access and it
doesn't use the same name for these functions. The functions that you
might wish to use in this case are:
Access: Nz(expr[, expr])
SQL Server: ISNULL(expr, expr) or COALESCE(expr[, expr[, ...]])
ISNULL will return the second expression if the first is NULL. COALESCE
will return the first non-NULL expression (so with two expressions, it is
the same as ISNULL; with more expressions it is equivalent to nested
ISNULL expressions).
Access: Isnull(expr)
SQL Server: expr IS NULL
Evaluates to true if the expression is equal to NULL, false otherwise.
Access: IIf(expr, truepart, falsepart)
SQL Server: CASE (see syntax below)
The access function checks the first expression, which should be a logical
expression. If it's true, the truepart is returned; else the falsepart. In
SQL Server, this can be replaced by the (very powerful) CASE expression,
which has two variants.
1. Searched CASE:
CASE
WHEN boolean-expr1 THEN result-expr1
WHEN boolean-expr2 THEN result-expr2
.....
ELSE else-result-expr
END
If boolean-expr1 evaluates to true, result-expr1 is returned. If not, SQL
Server will go on to evaluate boolean-expr2 and return result-expr2 if
that one is true. This will continue until a logical expression is found
that evaulates to true or until all boolean expressions have been
evaluated; if the last one evaluates to false as well, else-result-expr is
returned.
2. Simple CASE:
CASE input-expr
WHEN value-expr1 THEN result-expr1
WHEN value-expr2 THEN result-expr2
.....
ELSE else-result-expr
END
This is really just a shorthand for
CASE
WHEN input-expr = value-expr1 THEN result-expr1
WHEN input-expr = value-expr2 THEN result-expr2
.....
ELSE else-result-expr
END
After all this theory, let's have a look at your query. In SQL Server, you
could write this as
SELECT personid, firstname + ' ' + COALESCE (middlename + ' ', '')
+ lastname) AS FullName
FROM mydatabase
(Though I would recommend you to choose another name for your table. A
database is not the same thing as a table; you can't select FROM a
database)
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: KK: "Isn't Microsoft going to update English Query?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
