Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

WHERE clause on SQL Server "Text" data type

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?

like image 795
mmcglynn Avatar asked Dec 03 '10 21:12

mmcglynn


People also ask

Is text a data type in SQL?

TEXT is a variable width character string data type that supports non-Unicode data in the code page of a SQL database server and with a maximum string length of 2,147,483,647. This data type is used for storing large pieces of string data values.

How do I compare text data types in SQL?

SELECT STRCMP(argument1, argument2); Here, argument1 and argument2 are string type data values which we want to compare. The syntax for using LIKE wildcard for comparing strings in SQL is as follows : SELECT column_name1, column_name2,...

What is text datatype in SQL Server?

Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

Can we use WHERE clause in SQL?

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.


2 Answers

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.

like image 88
Martin Smith Avatar answered Oct 19 '22 05:10

Martin Smith


Please try this

SELECT * FROM   [Village] WHERE  CONVERT(VARCHAR, CastleType) = 'foo' 
like image 24
Emma Thapa Avatar answered Oct 19 '22 04:10

Emma Thapa