WHERE clause on SQL Server Text data type

Question

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

User · Answer

Please try this  SELECT   FROM    Village  WHERE  CONVERT VARCHAR  CastleType     foo

User · Answer

This works in MSSQL and MySQL   SELECT   FROM   Village WHERE  CastleType LIKE   foo

User · Answer

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

User · Answer

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

User · Answer

Another option would be   SELECT   FROM  Village  WHERE PATINDEX  foo    CastleType    lt  gt  0

User · Answer

That is not what the error message says  It says that you cannot use the   operator  Try for instance LIKE  foo

User · Answer

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

[sql-server] WHERE clause on SQL Server "Text" data type

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql