Passing parameters in CONTAINS using wildcard
-
Hello all.
I am having a difficult time trying to find a way to use an SQL string in ASP.NET to pass a parameter to the CONTAINS function using the wildcard. The problem is the single and double quotes. I even tried embedding a string variable in the string with no luck.
When you use pre-defined words in this function, it is easy and straight forward. However, I have a string in my ASP.NET code that is enclosed in a string and the further complication is I am wanting to pass a parameter from the user . So, the issue becomes that I want to pass a parameter inside the CONTAINS function using the wildcard. This did not work:
Dim strSQL as String
strSQL = "SELECT product_name, isbn FROM products " & _
" WHERE CONTAINS (product_name, '@catalog*' ) "
The @catalog parameter is passed in my SQL string statement using .NET.
This also didn't work using an embedded variable:
Dim strSQL as String
Dim strInput as String
strSQL = "SELECT product_name, isbn FROM products " & _
" WHERE CONTAINS (product_name, '" & """ & strInput & "*" & """ & "')"
Anyone have any thoughts/solutions?
-
Hi Harry,
Used very little .Net but if this was straight VB6 you seem to have got carried away with the double quotes.....
Dim strSQL As String
Dim strInput As String
strInput = "Roger"
strSQL = "SELECT product_name, isbn FROM products " & _
"WHERE CONTAINS (product_name, '" & strInput & "*')"
Form1.Print strSQL
Hope it helps.
Have fun
Steve
We need men who can dream of things that never were.
-
Just to add 1c to Steve's words.....
If your string contains any SINGLE quote you should change it from A SINGLE QUOTE to TWO CONTIGUOUS SINGLE QUOTE, as in:
Dim strSQL As String
Dim strInput As String
strInput = "Roger"
strInput = replace(strInput,"'","''")
strSQL = "SELECT product_name, isbn FROM products " & _
"WHERE CONTAINS (product_name, '" & strInput & "*')"
Form1.Print strSQL
Note: second operand in replace is:
DOUBLEQUOTE SINGLEQUOTE DOBLEQUOTE
and third is:
DOUBLEQUOTE SINGLEQUOTE SINGLEQUOTE DOBLEQUOTE
HTH
Luigi
-
I think both suggestions are good ones, but I am still having no success.
I resorted to writing a stored procedure instead of messing with the quotes in the string, but SQL Server is still not giving me what I need. I want the CONTAINS to act similar to the LIKE, but using the full-text search capabilities. That’s why I want the wildcard in there. However, the complication is passing a parameter AND using the wildcard. Here is one version of my stored procedure that doesn’t work and I am testing it against the Customer table in the Northwind DB:
CREATE PROCEDURE SearchCustomers
(
@CustVar varchar(50)
)
AS
SELECT *
FROM Customers
WHERE CONTAINS(*, '"@CustVar + *"')
GO
However, I get no results when I pass a value to this stored procedure.
When I use it without the wildcard it works?!
When I type in “Sale”, I want to see these types of results in my ASP.NET datagrid coming from the indexed column “ContactTitle”:
CustomerID
CompanyName
ContactName
ContactTitle
Address
City
Region
PostalCode
Country
Phone
Fax
ALFKI
Alfreds Futterkiste
Maria Anders
Sales Representative
Obere Str. 57
Berlin
12209
Germany
030-0074321
030-0076545
AROUT
Around the Horn
Thomas Hardy
Sales Representative
120 Hanover Sq.
London
WA1 1DP
UK
(171) 555-7788
(171) 555-6750
BLAUS
Blauer See Delikatessen
Hanna Moos
Sales Representative
Forsterstr. 57
Mannheim
68306
Germany
0621-08460
0621-08924
BSBEV
B's Beverages
Victoria Ashworth
Sales Representative
Fauntleroy Circus
London
EC2 5NT
UK
(171) 555-1212
Am I missing something else?
Thanks,
Harry
-
Hi Harry,
If what youv'e posted is exactly the same as your query, youv'e just missed a couple of quotes:-
SELECT *
FROM Customers
WHERE CONTAINS(*, '"@CustVar + *"')
GO
You are actually asking it to interpret @CustVar* instead of @CustVar + '*'
Unfortunately, we haven't got the Northwind db to practice on or I would have posted it correctly for you...... The joys of Management space saving
Have Fun
Steve
We need men who can dream of things that never were.
-
Hi Again,
Seeing i'd never used CONTAINS before, thought I would set up a table and have a play with it.
I found it easier to build the string seperately, and just add the Input into it:
DECLARE @InputVariable varchar(255),
@CustVar varchar(255)
SET @InputVariable = 'J'
SET @CustVar = '"'
SET @CustVar = @CustVar + @InputVariable
SET @CustVar = @CustVar + '*"'
SELECT *
FROM TESTNames
WHERE CONTAINS(*, @CustVar )
If you use @InputVariable as an Input Variable (obviously don't SET it within the sproc
) and use @CustVar to build the string within the sproc, this should work fine.That was a bit of a pain..........
Have fun
Steve
We need men who can dream of things that never were.
-
I got it to work! Thanks guys for all your help!
It is messy, but when I went into SQL Query Analyzer, I used the PRINT statement on the variable to see how the quotes and values looked. I noticed that anything inside single quotes is interpreted as a literal string value. So, I created a quote variable and wildcard variable and concatenated it all with the passed in parameter. It works perfect.
CREATE PROCEDURE SearchCustomers
(
@CustVar varchar(30)
)
AS
DECLARE @MyQuote varchar(10)
DECLARE @MyWildCard varchar(5)
SET @MyQuote = '"'
SET @MyWildCard = '*'
SET @CustVar = @MyQuote + @CustVar + @MyWildCard + @MyQuote
SELECT *
FROM Customers
WHERE CONTAINS(*, @CustVar)
PRINT @CustVar
GO
So, the single quotes encompass a double quote and enclosing the parameter and wildcard, we get the format the CONTAINS function needs to match if we would put in just straight text, like so:
CREATE PROCEDURE SearchCustomers
(
@CustVar varchar(50)
)
AS
SELECT *
FROM Customers
WHERE CONTAINS(*, '"Sal*"')
GO
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply