My DOTD : MS SQL Check for empty String

SQL ServerAs 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?

Published by Bramus!

Bramus is a frontend web developer from Belgium, working as a Chrome Developer Relations Engineer at Google. From the moment he discovered view-source at the age of 14 (way back in 1997), he fell in love with the web and has been tinkering with it ever since (more …)

Join the Conversation

8 Comments

  1. 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 ;)

  2. 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.

Leave a comment

Leave a Reply to bill Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.