Keyword search with PATINDEX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Keyword search with PATINDEX

Author

Topic

agenda9533
Starting Member

16 Posts

Posted - 2005-07-04 : 18:55:07

I want to create some powerful report that returns rows contained a pre-specified keyword (prompted variable @keyword).

Searchable field is Ticket.problem.
The dataset query:

SELECT Solutions.solution, Ticket.problem
FROM Ticket INNER JOIN
Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE (PATINDEX('%' + @keyword + '%', Ticket.problem) >= 1)

if I set @keyword ='customer' I'll receive Error: Aplication uses a value of the wrong type for current operation.
BUT the same query is working fine in Query Analyser:
declare @keyword varchar(100)
set @keyword = 'Customer Service'
SELECT Solutions.solution, Ticket.problem
FROM Ticket INNER JOIN
Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE (PATINDEX('%' + @keyword + '%', Ticket.problem) >= 1)

Please, HEELP!

jhermiz

3564 Posts

Posted - 2005-07-04 : 23:30:11

quote:
Originally posted by agenda9533

I want to create some powerful report that returns rows contained a pre-specified keyword (prompted variable @keyword).

Searchable field is Ticket.problem.
The dataset query:

SELECT Solutions.solution, Ticket.problem
FROM Ticket INNER JOIN
Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE (PATINDEX('%' + @keyword + '%', Ticket.problem) >= 1)

if I set @keyword ='customer' I'll receive Error: Aplication uses a value of the wrong type for current operation.
BUT the same query is working fine in Query Analyser:
declare @keyword varchar(100)
set @keyword = 'Customer Service'
SELECT Solutions.solution, Ticket.problem
FROM Ticket INNER JOIN
Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE (PATINDEX('%' + @keyword + '%', Ticket.problem) >= 1)

Please, HEELP!

Please post the entire procedure what type is @keyword, btw why aren't you just using a "LIKE" query ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

agenda9533
Starting Member

16 Posts

Posted - 2005-07-05 : 09:26:57

quote:
Originally posted by jhermiz

quote:
Originally posted by agenda9533

I want to create some powerful report that returns rows contained a pre-specified keyword (prompted variable @keyword).

Searchable field is Ticket.problem.
The dataset query:

SELECT Solutions.solution, Ticket.problem
FROM Ticket INNER JOIN
Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE (PATINDEX('%' + @keyword + '%', Ticket.problem) >= 1)

if I set @keyword ='customer' I'll receive Error: Aplication uses a value of the wrong type for current operation.
BUT the same query is working fine in Query Analyser:
declare @keyword varchar(100)
set @keyword = 'Customer Service'
SELECT Solutions.solution, Ticket.problem
FROM Ticket INNER JOIN
Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE (PATINDEX('%' + @keyword + '%', Ticket.problem) >= 1)

Please, HEELP!

Please post the entire procedure what type is @keyword, btw why aren't you just using a "LIKE" query ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

1. It is not a procedure, it is just dataset SELECT query. So &keyword datatype is String, Ticket.problem Varchar(128).
2. I'll test it with the 'like' predicate. In Oracle it wasn't so powerful - it didn't allow using % wildcard befor the pattern.
THANK YOU!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources