[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Contain Statement with Wildcard

Posted on 2012-08-27
17
Medium Priority
?
1,012 Views
Last Modified: 2012-08-31
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
Comment
Question by:browe68
  • 6
  • 5
  • 2
  • +2
17 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38338384
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
 

Author Comment

by:browe68
ID: 38338568
This is a Full Text Index Search and it uses 'CONTAINS'  Not 'LIKE'.
0
 
LVL 8

Expert Comment

by:Wikkard
ID: 38338675
You might consider using FREETEXT instead of CONTAINS.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:Eugene Z
ID: 38339007
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38339010
check this publication:

A Google-like Full Text Search

By Michael Coles
http://www.sqlservercentral.com/articles/Full-Text+Search+%282008%29/64248/
0
 
LVL 8

Expert Comment

by:Wikkard
ID: 38339160
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
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 2000 total points
ID: 38339432
Then try:
WHERE CONTAINS(nMemo, '"*' + @Text + '"')
--or
WHERE CONTAINS(nMemo, '"' + @Text + '*"')
--or
WHERE CONTAINS(nMemo, '"*' + @Text + '*"')

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38340778
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
 

Author Closing Comment

by:browe68
ID: 38342339
This worked...  Thanks for all the assistance


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

WHERE CONTAINS(nMemo, @Text)
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38344482
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38345204
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38345229
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38350431
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
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38351396
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38351508
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
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38353284
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38355429
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question