SQL Server Is there an easy way to ignore quotation marks when searching?

Question

I have a website where I need to search for data and ignore all quotes.

• The search is not performed, dont or dont, and retrieves results for strings that have words that begin with: do not, dont or dont
• Search for "hello" or "hello" or welcome search results for lines that begin with the words: "hello", "hello" or hello.

Note. I am already removing quotes for passed in the search bar

I want to know if there is a simpler (or less verbose) method than:

select Name from tbl_MyTable where (Replace(Replace(Replace(Replace(Replace(Replace(Name,'"',''),''',''),'''',''),'"',''),''',''),'"','') like 'dont%' or Replace(Replace(Replace(Replace(Replace(Replace(Name,'"',''),''',''),'''',''),'"',''),''',''),'"','') like '% dont%' ); 

Right now, my best idea is to create a new column containing a quotation-delimited version (added by a space) so I can just do:

 select Name from tbl_MyTable where FixedName like '% dont%'; 

But I really would like to know if this can be done without creating a new column and be efficient.

Answer 1

Use the full text index instead of LIKE.

Create your full-text index:

http://msdn.microsoft.com/en-us/library/ms187317.aspx

 CREATE UNIQUE INDEX ix1 ON tbl_MyTable(YourKey); //unique index required CREATE FULLTEXT CATALOG ft AS DEFAULT; // ft is your freetext catalog name CREATE FULLTEXT INDEX ON tbl_MyTable(Name) KEY INDEX ix1 WITH STOPLIST = SYSTEM; // this is your index and allows you to run the command below 

Then use this to fulfill your request:

 SELECT Name FROM tbl_MyTable WHERE FREETEXT(Name, 'dont'); 

This is the fastest technique for this kind of thing. You can get it even faster if you use third-party free text engines, but there is probably no need for this.

Answer 2

I would suggest creating a user-defined function to consolidate this logic:

 CREATE FUNCTION [dbo].[udf_StripQuotes] ( @String VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN RETURN Replace( Replace( Replace( Replace( Replace( Replace(@String,'"',''), ''',''), '''',''), '"',''), ''',''), '"','') END GO 

What then looks like:

 select Name from tbl_MyTable where dbo.udf_StripQuotes(name) like '% dont%'; 

In terms of efficiency, the leading and ending % in your like statement will not allow you to use any indexes, which will lead to a full table scan ... this is probably the greatest success in this query.

However, as Aaron explains, this implementation will be slower than the original due to the overhead of calling the UDF.

If you can avoid the starting template, then a computed column with an index is likely to improve performance.

Otherwise, I think your only option is to implement Full Text Search .

Answer 3

This is not an answer to the question, but it will be very difficult to implement as a comment.

If you intend to use UDF to simplify the query itself, do yourself a favor and limit the function calls to the number of lines you have, not two. Instead:

  where dbo.udf_StripQuotes(name) like 'dont%' or dbo.udf_StripQuotes(name) like '% dont%' ); 

Do it:

  where ' ' + dbo.udf_StripQuotes(name) like '% dont%'; 

Regarding the main issue, I agree with Michael that the indexed computed column may be better, but it will not be possible if the name column exceeds 900 bytes (and it won’t magically scan for searches, because of the wildcards, it’s just removes the need to call a function or execute all those that replace the calls in the request).

Answer 4

Effectively in space or in time?

Your first decision is economical in size, but most likely time-inefficient due to the use of several string functions for each row in the table each time the query is executed.

The solution to the created column is spatially inefficient, but most likely time-efficient due to the application of string operations once (when you add the column and then to insert / update).

From the point of view of your users, the best solution is probably to search in the generated column.

Answer 5

Try the following to return all names without quotes or double quotes. This will eliminate the need for LIKE statements, avoid the need for another column, and speed up your query:

 SELECT Replace( Replace( Replace( Replace( Replace( Replace( Name, '"', ''), ''', ''), '''',''), '"', ''), ''',''), '"', '') AS Name FROM tbl_MyTable