1

In my SQL Server table called AAA I have field called BBB containing strings full of wild cards:

&abc##v

_&abc##&&

_&abc##_12

_&abc##%

I need to allow user to search for partial matches on this field.
User's search phrase will contain lots of wild cards.
What is the best approach for this problem?

I guess I could write something like:

select * from AAA where BBB like '%' + 
(add escape character to every wild card found in user's question) + '%' 
escape 'whatever char is not being used in user's question'

But I do not like this idea. Is there any better one?

NotMe
  • 87,343
  • 27
  • 171
  • 245
Jacek Wojcik
  • 1,223
  • 2
  • 19
  • 33
  • 6
    can you explain why you don't like the approach you have? Seems like a pretty sensible way to go about it. – NotMe Oct 08 '13 at 22:49
  • It would work but seems to me as something too complicated. – Jacek Wojcik Oct 08 '13 at 23:16
  • There's not much complicated about this. Pretty standard that certain characters need to be escaped depending upon how they are processed. – NotMe Oct 08 '13 at 23:23

1 Answers1

0

OR you can make use of Full-text Search which gives you way better performance as compare to LIKE operator,I would Create full-text indexes the tables and make use of full-text search

DECLARE @userValue nvarchar(100) = 'some Value'
Declare @sql NVARCHAR(MAX);
SET @sql = '''''select * from myTable
WHERE CONTAINS (myColumn, "' + @userValue + '")'''''

EXECUTE sp_Executesql @sql
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Disagree on the "way better performance" part. Having extensively used full text search this is rarely true. – NotMe Oct 08 '13 at 23:20
  • Also FTS treats special characters as word delimiters and as such they won't appear in an index unless you go so far as to redefine those delimiters. See http://stackoverflow.com/questions/995478/sql-server-full-text-search-escape-characters – NotMe Oct 08 '13 at 23:22
  • so some extent you are right. but in some cases it does work better then LIKE operator. depends on what kind of query you are executing. In my personal experience I did gain way better performance using full-text search and returned only the results I actually needed making use of FORMSOF, Inflectional and thesaurus filters in FTS. – M.Ali Oct 08 '13 at 23:27
  • Well, that's why it exists: difference systems, different behavior. Either way, the answer won't help the OP due to issues with escaped characters and FTS. – NotMe Oct 08 '13 at 23:30
  • In this particular case when you have data like this '_&abc##&&' user will be searching for some human readable data not special characters, I think FTS will work better in this case. – M.Ali Oct 08 '13 at 23:31
  • You must have missed this in the OP: "User's search phrase will contain lots of wild cards." clearly they are searching for those characters. – NotMe Oct 08 '13 at 23:38