Sorting/Compare Problem in SQL Server

Question

I'm having trouble figuring out how to run a query in SQL Server. I want to run a query and select columns from a table and add a where statement too.

So like: SELECT *

FROM Product

WHERE Name = 'Some Name';

The data type for Name is text, but when I try to run a query it says text data type can't be sorted or compared except when using IS NULL or LIKE.

This type of query works in Access, have I setup my table wrong in SQL Server or something? This type of query should work shouldn't it?

Answer 1

I'm having trouble figuring out how to run a query in SQL Server. I want to run a query and select columns from a table and add a where statement too.

So like: SELECT *

FROM Product

WHERE Name = 'Some Name';

The data type for Name is text, but when I try to run a query it says text data type can't be sorted or compared except when using IS NULL or LIKE.

This type of query works in Access, have I setup my table wrong in SQL Server or something? This type of query should work shouldn't it?

The text data type is deprecated.  If you actual data length is under 8000 characters, you should instead use varchar(n), where n is the maximum length.  If your data can exceed 8000 characters, use varchar(MAX) in SQL 2005 and later, which will allow normal compare operations. 

With text, you can still use SUBSTRING for equality comparisions on the first 8000 characters:

SELECT *
FROM dbo.Product 
WHERE SUBSTRING(Name,1,8000) = 'Some Name';

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

In addition

WHERE PATINDEX('%'+ 'Some Name'+'%', Name )>0 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/