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?

Elsewhere

8 Responses to My DOTD : MS SQL Check for empty String

  1. Nathan says:

    There is a mysql_last_insert_id alike function for MS SQL and it’s called scope_identity πŸ™‚

  2. Bramus! says:

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

  3. Nathan says:

    lol, I didn’t see that part :p

  4. James says:

    Thanks!

  5. DJ Bunge says:

    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.

  6. Andy says:

    nice one, simple and straight forward πŸ™‚

  7. bk says:

    this totally worked for me…
    (NOT (myField LIKE ”))

  8. bill says:

    Great post and Great Answer! Thank for the post

Leave a 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.