SQLServer“ Text”数据类型上的 WHERE 子句

其中[ CastleType ]设置为 SQLServer 中的数据类型“ text”，查询为:

SELECT *
FROM   [Village]
WHERE  [CastleType] = 'foo'

我得到了一个错误:

数据类型 短信和 VARCHAR在等于操作符时是不兼容的。

我不能用 WHERE 子句查询此数据类型吗？

226595 次浏览
小开

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.

小开

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.

小开

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

小开

Another option would be:

SELECT * FROM [Village] WHERE PATINDEX('foo', [CastleType]) <> 0
小开

This works in MSSQL and MySQL:

SELECT *
FROM   Village
WHERE  CastleType LIKE '%foo%';
小开

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'
小开

Please try this

SELECT *
FROM   [Village]
WHERE  CONVERT(VARCHAR, CastleType) = 'foo'

提交答案