composite WHERE condition in SQL Server

25 pts.
SQL Server development
SQL Server tables
WHERE condition
In Oracle, I can do: Select * from table where field1 || field2 in ('01XX','02YY','03ZZ') ; Where field1 has values of 01,02,03 and field2 has values of XX,YY,ZZ etc Can I do the same thing in SQL Server, or do I need define some derived fields to get the same effect?

Software/Hardware used:
SQL Server

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes, you can.

<pre>SELECT * FROM yourTable
WHERE field1+field2 IN (’01XX’,’02YY’,’03ZZ’);</pre>

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • MarcW
    Thanks- that works. In this instance, the version field is a derived field, because the field in the database is an 11-char int for some reason. So it looks like this: SELECT code, right('0' + convert(VARCHAR,codeversion),2) AS ver FROM table WHERE code + right('0' + convert(VARCHAR,codeversion),2) IN ('00102','00202','00302') ; If I use WHERE code + ver IN ('00102','00202','00302') as the predicate, the SQL Server ODBC driver complains that "ver" is an invalid field name. I'm concerned that it's inefficient doing the conversion multiple times. Am I missing a trick here?
    25 pointsBadges:
  • carlosdl
    Since you are using concatenations and conversions in your where clause, the optimizer is not using any indexes and is already doing a full table scan to get your data, so, I think you could avoid the double conversion by doing something like this:
    SELECT code, ver
    FROM ( 
    SELECT code, right(’0′ + convert(VARCHAR,codeversion),2) AS ver
    FROM table) AS temp
    where code+ver IN (’00102′,’00202′,’00302′);
    84,580 pointsBadges:
  • Denny Cherry
    While you can do this keep in mind that any indexes that you have on the table won't be used. You'll get better performance if you add a computed column and index that column then use that column in your WHERE clause.
    68,815 pointsBadges:
  • MarcW
    Okay... I don't have any control over the table, this is an MIS application pulling from a database someone else has created. What we're pulling from is actually a snapshot: would it be better to ask them to create a field in the snapshot that's pre-formatted?
    25 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: