As a MySQL adept, it’s quite a change to query an MS SQL Server. Today I wanted to select all items from a table where a field wasn’t an empty string (really empty string as in ”, not as in being
NULL). Merely a
SELECT * FROM myTable WHERE myField != '' right? Wrong!
The differences between querying a MySQL and MS SQL Server are not that hard, if you know them: There is no
mysql_last_insert_id alike function for MS SQL, you have to work with identity columns and fire an extra query at the server to get the last inserted id; Instead of adding a
LIMIT 0, 10 to a query one has to select the
TOP 10; etc.
Back to the empty string checking … in MS SQL one cannot use the
<> '' on
varchar data types. Instead of selecting (in human lanugage) where the value differs from an empty string one has to select (again, human language) where the string is like a sequence of characters
[sql]SELECT * FROM myTable WHERE myField LIKE ‘_%'[/sql]
Yep, that’s right, the logic is a straight 180° (a U-turn) that the logic used in MySQL … or am I still an MS SQL n00b and overlooking something?
There is a mysql_last_insert_id alike function for MS SQL and it’s called scope_identity 🙂
Erm Nathan, check this quote from the post above: “There is no mysql_last_insert_id alike function for MS SQL, you have to work with identity columns and fire an extra query at the server to get the last inserted id;”
–>> that part about “fire an extra query at the server” is that
scope_identity you've mentioned ;)
lol, I didn’t see that part :p
For me, when I used LIKE ‘_%’ , my results were the same.
Using (NOT (myField LIKE ”)) didn’t return the empty strings, but also didn’t return the null values.
nice one, simple and straight forward 🙂
this totally worked for me…
(NOT (myField LIKE ”))
Great post and Great Answer! Thank for the post
Leave a comment