WHERE clause on SQL Server "Text" data type
Sql ServerSql Server-2005TsqlSql Server Problem Overview
Where [CastleType] is set as data type "text" in SQL Server and the query is:
SELECT *
FROM [Village]
WHERE [CastleType] = 'foo'
I get the error:
>The data types TEXT and VARCHAR are incompatible in the equal to operator.
Can I not query this data type with a WHERE clause?
Sql Server Solutions
Solution 1 - Sql Server
You can use LIKE
instead of =
. Without any wildcards this will have the same effect.
DECLARE @Village TABLE
(CastleType TEXT)
INSERT INTO @Village
VALUES
(
'foo'
)
SELECT *
FROM @Village
WHERE [CastleType] LIKE 'foo'
text
is deprecated. Changing to varchar(max)
will be easier to work with.
Also how large is the data likely to be? If you are going to be doing equality comparisons you will ideally want to index this column. This isn't possible if you declare the column as anything wider than 900 bytes though you can add a computed checksum
or hash
column that can be used to speed this type of query up.
Solution 2 - Sql Server
Please try this
SELECT *
FROM [Village]
WHERE CONVERT(VARCHAR, CastleType) = 'foo'
Solution 3 - Sql Server
You can't compare against text
with the =
operator, but instead must used one of the comparison functions listed here. Also note the large warning box at the top of the page, it's important.
Solution 4 - Sql Server
If you can't change the datatype on the table itself to use varchar(max), then change your query to this:
SELECT *
FROM [Village]
WHERE CONVERT(VARCHAR(MAX), [CastleType]) = 'foo'
Solution 5 - Sql Server
That is not what the error message says. It says that you cannot use the =
operator. Try for instance LIKE 'foo'
.
Solution 6 - Sql Server
Another option would be:
SELECT * FROM [Village] WHERE PATINDEX('foo', [CastleType]) <> 0
Solution 7 - Sql Server
This works in MSSQL and MySQL:
SELECT *
FROM Village
WHERE CastleType LIKE '%foo%';