• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1240
  • Last Modified:

SQL Contain Statement with Wildcard

I have a SQL Contain statement that is parameterized... How do I add a beginning or trailing wildcard?

WHERE CONTAINS(nMemo, @Text)

I need the @Text to have a trailing wildcard.

Thanks...
0
browe68
Asked:
browe68
  • 6
  • 5
  • 2
  • +2
1 Solution
 
Habib PourfardSoftware DeveloperCommented:
you could write:

for beginning:
WHERE nMemo LIKE '%' + @Text 

Open in new window

for trailing:
WHERE nMemo LIKE @Text + '%'

Open in new window

or both:
WHERE nMemo LIKE '%' + @Text + '%'

Open in new window



Wildcard          Description
%                    A substitute for zero or more characters
_                    A substitute for exactly one character
[charlist]                    Any single character in charlist
[^charlist] or [!charlist]          Any single character not in charlist
0
 
browe68Author Commented:
This is a Full Text Index Search and it uses 'CONTAINS'  Not 'LIKE'.
0
 
WikkardCommented:
You might consider using FREETEXT instead of CONTAINS.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Eugene ZCommented:
Please post some example of what you have and what you wish to have
"Search does not support the use of leading wildcards."

but you can try  extra ideas in the below article
How do you get leading wildcard full-text searches to work in SQL Server?
http://stackoverflow.com/questions/3400/how-do-you-get-leading-wildcard-full-text-searches-to-work-in-sql-server
0
 
Eugene ZCommented:
check this publication:

A Google-like Full Text Search

By Michael Coles
http://www.sqlservercentral.com/articles/Full-Text+Search+%282008%29/64248/
0
 
WikkardCommented:
Try something like

declare @searchParm varchar(120)
--set the search parameters
set @searchParm = '"this" AND "is" AND "a" AND "search" AND "item"'

select * from someTable where CONTAINS(nMemo, @searchParm )
0
 
Habib PourfardSoftware DeveloperCommented:
Then try:
WHERE CONTAINS(nMemo, '"*' + @Text + '"')
--or
WHERE CONTAINS(nMemo, '"' + @Text + '*"')
--or
WHERE CONTAINS(nMemo, '"*' + @Text + '*"')

Open in new window

0
 
Anthony PerkinsCommented:
pourfard,
WHERE CONTAINS(nMemo, '"*' + @Text + '"')
As indicated previously Full-Text search does not support leading wildcards.  So there is no point in trying, let alone suggesting it.
0
 
browe68Author Commented:
This worked...  Thanks for all the assistance


Select @Text = '"' + @Text + '*"'

WHERE CONTAINS(nMemo, @Text)
0
 
Habib PourfardSoftware DeveloperCommented:
acperkins,
instead of telling me what not to suggest, Isn't it better to offer you own solution if you have any?
To learn how to query with Full-Text Search take a look at MSDN > Query with Full-Text Search > Supported Forms of Query Terms
0
 
Anthony PerkinsCommented:
pourfard,
instead of telling me what not to suggest
I am sorry, I just want to make sure I understand you are stating that Full-Text Search supports leading wildcards as in your posted solution:
WHERE CONTAINS(nMemo, '"*' + @Text + '"')

Again I am sorry if I misunderstood, but I thought you had said that.  

If the answer is yes (and I have to admit I am very dense), but do you mind pointing out the exact line in that article that states that Full-Text Search supports leading wildcards, I am having really hard time finding it.
0
 
Anthony PerkinsCommented:
pourfard,

Isn't it better to offer you own solution if you have any?
I realize you have not been here very long, so you may not be familiar with the EE Guidelines, but there is a rule that if someone has already given a solution you do not duplicate it.  You gave the only valid answer in your first comment using LIKE (http:#a38338384).  There was simply no point in me repeating that (aside from the discourtesy), other than to say that it was not possible using Full-Text Search.
0
 
Anthony PerkinsCommented:
pourfard,

I am going to assume that you agree with me and that the following is not supported using Full-Text Search:
WHERE CONTAINS(nMemo, '"*' + @Text + '"')

I suspect you would agree with me, in that we would not want future readers to misunderstand the accepted solution.
0
 
Habib PourfardSoftware DeveloperCommented:
acperkins,
Of course it is supported. The following text is copied from the link I've provided on my previous comment:

Performing Prefix Searches (Prefix Term)

You can use CONTAINS or CONTAINSTABLE to search for words or phrases with a specified prefix. All entries in the column that contain text beginning with the specified prefix are returned. For example, to search for all rows that contain the prefix top-, as in topple, topping, and top. The query looks like this:

USE AdventureWorks2012
GO

SELECT Description, ProductDescriptionID
FROM Production.ProductDescription
WHERE CONTAINS (Description, '"top*"' )
GO
All text that matches the text specified before the asterisk (*) is returned. If the text and asterisk are not delimited by double quotation marks, as in CONTAINS (DESCRIPTION, 'top*'), full-text search does not consider the asterisk to be a wildcard..

When the prefix term is a phrase, each token making up the phrase is considered a separate prefix term. All rows that have words beginning with the prefix terms will be returned. For example, the prefix term "light bread*" will find rows with text of "light breaded," "lightly breaded," or "light bread," but it will not return "lightly toasted bread".
0
 
Anthony PerkinsCommented:
pourfard,

With all due respect you are missing the point:

It is not called a Prefix Search for nothing.  Full-Text Search does not support leading wildcards, it only supports trailing wildcards.

The original question was "I have a SQL Contain statement that is parameterized... How do I add a beginning or trailing wildcard?" and the answer is that you cannot use Full-Text Search with a leading wildcard. As you correctly stated in your first comment you have to use LIKE for that.

In other words, the only condition supported in your accepted solution http:#a38339432:
WHERE CONTAINS(nMemo, '"*' + @Text + '"')
WHERE CONTAINS(nMemo, '"' + @Text + '*"')
WHERE CONTAINS(nMemo, '"*' + @Text + '*"')

Is the second one, the other two are not.

But if you still feel I am wrong, feel free to find some documentation that states that Full-Text Search supports leading wildcards or even better an example I can run that proves it.
0
 
Habib PourfardSoftware DeveloperCommented:
acperkins,
My final words:
1. Sql consider the asterisk to be a wildcard If the text and asterisk are delimited by double quotation marks. asterisk in "*word" and "word*" are both wildcard. so you can not say it is wrong.
2. example: "this is an example"
each of the following queries
WHERE CONTAINS(nMemo, '"*example"')  
or
WHERE CONTAINS(nMemo, '"example"')  
which are equal work fine, if * is not a wildcard so how does it work??? I'm sure the author was not looking for something like '"*mple"' that's why my solution is accepted.
0
 
Anthony PerkinsCommented:
It is evident you have never used Full-Text Search with a Prefix search and I am sorry to put it bluntly but you are wrong.

I don't expect you to admit it, but to all future readers, I would urge you to read the documentation in SQL Server's BOL carefully.  In particular this section taken from the CONTAINS section is relevant (my emphasis):
<prefix_term>
Specifies a match of words or phrases beginning with the specified text. Enclose a prefix term in double quotation marks ("") and add an asterisk (*) before the ending quotation mark, so that all text starting with the simple term specified before the asterisk is matched. The clause should be specified this way: CONTAINS (column, '"text*"'). The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase). If the text and asterisk are not delimited by double quotation marks, so the predicate reads CONTAINS (column, 'text*'), full-text search considers the asterisk as a character and searches for exact matches to text*. The full-text engine will not find words with the asterisk (*) character because word breakers typically ignore such characters.

When <prefix_term> is a phrase, each word contained in the phrase is considered to be a separate prefix. Therefore, a query specifying a prefix term of "local wine*" matches any rows with the text of "local winery", "locally wined and dined", and so on.
I trust that is clear, if it is not, notice how there are no examples using leading wildcards, which is not surprising as the documentation does not cover that case.  If you are still not convinced test it out for yourself and feel free to post here a SQL script that proves me wrong.  I will be the first to admit it and bring it up to the MVP community so that BOL can be corrected.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now