 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » Coalesce and better?... |
|
Page 1 of 1 |
|
| Author |
Message |
| Don84... |
 Posted: Fri Oct 02, 2009 2:28 pm |
|
|
|
Guest
|
Coalesce would return the first match of expression arguments. I'm
wondering if there's a similar function that would return any (all)
matches of any argument.
records samples of table "XYZ":
id txt
1 good
2 better
3 best
4 nuts
select txt from XYZ
where txt = Coalesce('good','better','best')
would return the row of id 1
but I'd like to be able to return rows of 1, 2 and 3 with a single
function similar to Coalesce without using UNION or OR. Possible?
Thanks. |
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Fri Oct 02, 2009 6:35 pm |
|
|
|
Guest
|
You can use the IN predicate:
SELECT txt
FROM XYZ
WHERE txt IN ('good', 'better', 'best');
The purpose of the COALESCE function is completely different, it is for handling NULLs.
--
Plamen Ratchev
http://www.SQLStudio.com |
|
|
| Back to top |
|
|
|
| Don84... |
| Posted: Fri Oct 02, 2009 7:39 pm |
|
|
|
Guest
|
On Oct 2, 11:49 am, Banana <Ban... at (no spam) Republic.com> wrote:
Quote: Plamen Ratchev wrote:
You can use the IN predicate:
SELECT txt
FROM XYZ
WHERE txt IN ('good', 'better', 'best');
The purpose of the COALESCE function is completely different, it is for
handling NULLs.
I wonder if that question has to do with parameterizing the IN
predicate, which can't be done. If so, a quick'n'slow answer could be to
do a CHARINDEX()>0 but you can kiss performance good bye.
If you need performance, I'd probably use a temp table and join it. Take
look at Erland's excellent treatise:
http://www.sommarskog.se/dynamic_sql.html
Embarassing, I was totally brain-dead this morning, even forgot the IN
operator. And Banaba, yes, performance is important. Funny thing,
I'm using an open source web programming language, it simply hates the
loop
-- just the idea
select txt from XYZ
where txt IN (loop index=i list="good,better,best">'#i#'</loop>,0)
so, I use UNION for them, and also UNION seems good for performance. |
|
|
| Back to top |
|
|
|
| Banana... |
| Posted: Fri Oct 02, 2009 7:49 pm |
|
|
|
Guest
|
Plamen Ratchev wrote:
Quote: You can use the IN predicate:
SELECT txt
FROM XYZ
WHERE txt IN ('good', 'better', 'best');
The purpose of the COALESCE function is completely different, it is for
handling NULLs.
I wonder if that question has to do with parameterizing the IN
predicate, which can't be done. If so, a quick'n'slow answer could be to
do a CHARINDEX()>0 but you can kiss performance good bye.
If you need performance, I'd probably use a temp table and join it. Take
look at Erland's excellent treatise:
http://www.sommarskog.se/dynamic_sql.html |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Fri Dec 11, 2009 10:34 pm
|
|