Re: Select Expression Operators

• Previous message: Hugo Kornelis: "Re: convert from hexadecimal to a decimal"
• Messages sorted by: [ date ] [ thread ]

Date: Thu, 30 Sep 2004 21:05:04 -0700

Thanks Hugo. I've already found that info out (me shoulda looked first, me
very dumb), but thank you very much for answering and doing a complete job as
well. :)

"Hugo Kornelis" wrote:

> 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)
>

• Previous message: Hugo Kornelis: "Re: convert from hexadecimal to a decimal"
• Messages sorted by: [ date ] [ thread ]