0

I've been banging my head against a brick wall for this for ages.

I am trying to query a table in SQL Express 2010. There are about ten fields in the table. One of the fields is a text field. Some of these have text in them, some have none (I am presuming that they are empty rather than null because querying with Is Null returns an empty set). I want to return the records which have text in this field and not those which are empty. I am guessing that they are empty rather than a blank space.

I have tried the NullIf and IsNull combinations I have seen posted in a few forums but the syntax in these are causing errors. When I put in ' ' as the empty string it is highlighted in red as an error and executing the query just returns an error about can not find such and such with this column name. Sorry I don't have the exact phrase, I'm also having problems with a monitor, etc, etc...

Hope you can help, let me know if you need any more info. Thanks!

HMcGoonish
  • 57
  • 7

1 Answers1

0

This bit of code shows how to handle NULL and/or "emptry string" text fields:

CREATE TABLE #TextTest (
    ID INT IDENTITY,
    TextCol TEXT
)

--Add 2 rows with data, 1 with NULL, 1 with "empty string".
INSERT INTO #TextTest (TextCol)
VALUES ('123'),('456'), (NULL), ('')

--This should succeed, but returns the one row with the "empty string".
SELECT *
FROM #TextTest
WHERE TextCol IS NOT NULL

--This should fail
SELECT *
FROM #TextTest
WHERE TextCol <> ''

--This should succeed.
SELECT *
FROM #TextTest
WHERE CAST(TextCol AS VARCHAR(MAX)) <> ''


--Clean up temp table when finished.
--DROP TABLE #TextTest

NOTE: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Dave Mason
  • 4,746
  • 2
  • 23
  • 24