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?
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.
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,...
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.
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.
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.
Please try this
SELECT * FROM [Village] WHERE CONVERT(VARCHAR, CastleType) = 'foo'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With