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 text
, ntext
and 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
Thanks!
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