Site Search Code Help

[EvoEight]

I'm writing a search for our site and I'm running into a few problems.
When I run the query like this, it runs just fine:

Declare @Keywords varchar(2000)
Select @Keywords = 'modern trial advocacy canada'
Select product_id, name, count(name) hits
FROM estore_products
INNER JOIN sequence
ON estore_products.name like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'
WHERE
seq <= len(' ' + @keywords + ' ') and
Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0
Group by estore_products.product_id, name
ORDER BY Hits DESC

But when I add another column (for example a column called description)
from the select statement I get this error:

The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Long story short, what do I need to do to select more columns for the
final output?

Thanks,
Ryan

[John Bell]

Hi Ryan

Have you read http://www.sommarskog.se/dyn-search.html ?

John

<Ev******@gmail.comwrote in message
news:11*********************@79g2000cws.googlegrou ps.com...

I'm writing a search for our site and I'm running into a few problems.
When I run the query like this, it runs just fine:

Declare @Keywords varchar(2000)
Select @Keywords = 'modern trial advocacy canada'
Select product_id, name, count(name) hits
FROM estore_products
INNER JOIN sequence
ON estore_products.name like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'
WHERE
seq <= len(' ' + @keywords + ' ') and
Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0
Group by estore_products.product_id, name
ORDER BY Hits DESC

But when I add another column (for example a column called description)
from the select statement I get this error:

The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Long story short, what do I need to do to select more columns for the
final output?

Thanks,
Ryan

[Erland Sommarskog]

Ev******@gmail.com (Ev******@gmail.com) writes:

I'm writing a search for our site and I'm running into a few problems.
When I run the query like this, it runs just fine:

Declare @Keywords varchar(2000)
Select @Keywords = 'modern trial advocacy canada'
Select product_id, name, count(name) hits
FROM estore_products
INNER JOIN sequence
ON estore_products.name like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'
WHERE
seq <= len(' ' + @keywords + ' ') and
Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0
Group by estore_products.product_id, name
ORDER BY Hits DESC

But when I add another column (for example a column called description)
from the select statement I get this error:

The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Long story short, what do I need to do to select more columns for the
final output?

What is the data type of that column? And how does the query with that
column look like?

You would only get this error if you are using the text/ntext/image
data type.

What version of SQL Server are you using? On SQL 2005, there is no
need to use text & co, as there are new data types (n)varchar(MAX)
and varbinary(MAX) which can fit just as much data as text & co,
but which does not have all restrictions of the old types.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[EvoEight]

I changed the data type and now I have the correct columns being
searched but now I have a new problem. When I search for a program
name, it returns just fine (the first part of the union) but when I
search for a persons name (the latter half of the union) I need it to
return the program's name from estore_products not the name from
crm_contact_publishers. Any ideas on how to get it to output the name
column from estore_products? The product IDs are the same on each
table. TIA
Ryan
My code:

Declare @Keywords varchar(2000)
Select @Keywords = 'zwier' -- Static for now and we'll pass it off
later from vb
Select name, product_id, count(name) hits
FROM estore_products
INNER JOIN sequence
ON estore_products.name like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'
WHERE
seq <= len(' ' + @keywords + ' ') and
Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0
Group by product_id, name, description
UNION
Select name, estore_product_id, count(name) hits
FROM crm_contact_publishers
INNER JOIN sequence
ON crm_contact_publishers.name like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'
WHERE
seq <= len(' ' + @keywords + ' ') and
Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0
Group by name, estore_product_id
ORDER BY Hits DESC

[Erland Sommarskog]

Ev******@gmail.com (Ev******@gmail.com) writes:

I changed the data type and now I have the correct columns being
searched but now I have a new problem. When I search for a program
name, it returns just fine (the first part of the union) but when I
search for a persons name (the latter half of the union) I need it to
return the program's name from estore_products not the name from
crm_contact_publishers. Any ideas on how to get it to output the name
column from estore_products?

Could be as easy as?

Select e.name, estore_product_id, count(name) hits
FROM crm_contact_publishers p
JOIN estore_products e ON c.product_id = p.product_id
INNER JOIN sequence
ON crm_contact_publishers.name like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'
WHERE
seq <= len(' ' + @keywords + ' ') and
Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0
Group by name, estore_product_id
ORDER BY Hits DESC

There is a good recommendation for posting queries like this and
that is that you post:

o CREATE TABLE statements for your tables, preferably with
PRIMARY and FOREIGN KEY definitions.
o INSERT statements with sample data.
o The desired output with sample data.

That makes it possible to develop a tested solution. The less information
you post, the wilder the guesses you will be you get in response.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx