Using wildcards in a WHERE statement

[David C. Holley]

I'm in the process of converting from Access to SQLServer and need help
with using wild cards in a WHERE statement. When the following statement
is executed, the error message
'Invalid operator for data type. Operator equals boolean AND, type
equals varchar.' is returned.

SELECT DISTINCT
udtZone, udtLocationKey, udtLocationName, udtLocationAddressLine1,
udtLocationAddressLine2, udtLocationAddressCity,
udtLocationAddressZipCode, udtLocationTelephoneGeneral, charComplex,
varCharCategoryDescriptionLong, charAirportCode, vcharLocationType,
bitShowAddressByDefaultInResults
FROM view_SelectLocationsByAddressOrTelephone
WHERE (udtLocationAddressLine1 LIKE '%' & 'boulevard' & '%')
ORDER BY varCharCategoryDescriptionLong, udtLocationName;

In the final implementation 'boulevard' will be replaced with a variable.

David H

[Mark Schupp]

WHERE (udtLocationAddressLine1 LIKE '%boulevard%')


--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"David C. Holley" <DavidCHolley@netscape.net> wrote in message
news:eG298pbaEHA.3508@TK2MSFTNGP09.phx.gbl...

> I'm in the process of converting from Access to SQLServer and need help
> with using wild cards in a WHERE statement. When the following statement
> is executed, the error message
> 'Invalid operator for data type. Operator equals boolean AND, type
> equals varchar.' is returned.
>
> SELECT DISTINCT
> udtZone, udtLocationKey, udtLocationName, udtLocationAddressLine1,
> udtLocationAddressLine2, udtLocationAddressCity,
> udtLocationAddressZipCode, udtLocationTelephoneGeneral, charComplex,
> varCharCategoryDescriptionLong, charAirportCode, vcharLocationType,
> bitShowAddressByDefaultInResults
> FROM view_SelectLocationsByAddressOrTelephone
> WHERE (udtLocationAddressLine1 LIKE '%' & 'boulevard' & '%')
> ORDER BY varCharCategoryDescriptionLong, udtLocationName;
>
> In the final implementation 'boulevard' will be replaced with a variable.
>
> David H
>
>

[Aaron [SQL Server MVP]]

& is not a string operator in T-SQL, it's a BITWISE operator! I think you
meant '%'+'boulevard'+'%' or just '%boulevard%'

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"David C. Holley" <DavidCHolley@netscape.net> wrote in message
news:eG298pbaEHA.3508@TK2MSFTNGP09.phx.gbl...

> I'm in the process of converting from Access to SQLServer and need help
> with using wild cards in a WHERE statement. When the following statement
> is executed, the error message
> 'Invalid operator for data type. Operator equals boolean AND, type
> equals varchar.' is returned.
>
> SELECT DISTINCT
> udtZone, udtLocationKey, udtLocationName, udtLocationAddressLine1,
> udtLocationAddressLine2, udtLocationAddressCity,
> udtLocationAddressZipCode, udtLocationTelephoneGeneral, charComplex,
> varCharCategoryDescriptionLong, charAirportCode, vcharLocationType,
> bitShowAddressByDefaultInResults
> FROM view_SelectLocationsByAddressOrTelephone
> WHERE (udtLocationAddressLine1 LIKE '%' & 'boulevard' & '%')
> ORDER BY varCharCategoryDescriptionLong, udtLocationName;
>
> In the final implementation 'boulevard' will be replaced with a variable.
>
> David H
>
>

[David Holley]

And completed its actually

strSQLStatement = strSQLStatement & "(udtLocationAddressLine1 Like '%" &
scrubCriteriaValue(session("rqs_searchCriteriaValu eAddress")) & "%') "

THX
David H

[url]www.gatewayorlando.com[/url]

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Aaron [SQL Server MVP]]

So, response.write strSQLStatement and show us what that yields. Tough for
us to tell you anything, since we don't know what the rest of
strSQLStatement is, we don't know what's in session("rqs_ridiculous_name")
and we don't know what scrubCriteriaValue() does.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"David Holley" <davidcholley@netscape.net> wrote in message
news:OQm0mRcaEHA.972@TK2MSFTNGP12.phx.gbl...

> And completed its actually
>
> strSQLStatement = strSQLStatement & "(udtLocationAddressLine1 Like '%" &
> scrubCriteriaValue(session("rqs_searchCriteriaValu eAddress")) & "%') "
>
> THX
> David H

[David Holley]

SELECT DISTINCT udtZone, udtLocationKey, udtLocationName,
udtLocationAddressLine1, udtLocationAddressLine2,
udtLocationAddressCity, udtLocationAddressZipCode,
udtLocationTelephoneGeneral, charComplex,
varCharCategoryDescriptionLong, charAirportCode, vcharLocationType,
bitShowAddressByDefaultInResults FROM
view_SelectLocationsByAddressOrTelephone WHERE (udtLocationAddressLine1
Like '%boulevard%') ORDER BY varCharCategoryDescriptionLong,
udtLocationName;

[url]www.gatewayorlando.com[/url]

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

David Holley wrote:

> SELECT DISTINCT udtZone, udtLocationKey, udtLocationName,
> udtLocationAddressLine1, udtLocationAddressLine2,
> udtLocationAddressCity, udtLocationAddressZipCode,
> udtLocationTelephoneGeneral, charComplex,
> varCharCategoryDescriptionLong, charAirportCode, vcharLocationType,
> bitShowAddressByDefaultInResults FROM
> view_SelectLocationsByAddressOrTelephone WHERE
> (udtLocationAddressLine1 Like '%boulevard%') ORDER BY
> varCharCategoryDescriptionLong, udtLocationName;
>

Nothing sticks out to me. I suggest pasting this statement into Query
Analyzer so you can debug it there. Start removing things until it runs
without error. That shoule give you a clue.
If you need further assistance, you will need to provide us with your table
(in the form of a CREATE TABLE statement) and some sample data (in the form
of INSERT ...VALUES statements) so we can reproduce the problem.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[David Holley]

Nothing sticks out as to why it doesn't work, because it works. I only
posted the full SQL statement because it sounded as if someone wanted to
see it in toto.

David H

[url]www.gatewayorlando.com[/url]

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[creejohnson@mindspring.com]

Hi David,

I'm in the process of doing the same migration and got the same error. Did you discover why? Thanks!

Chris

************************************************** ********************
Sent via Fuzzy Software @ [url]http://www.fuzzysoftware.com/[/url]
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

[David Holley]

Yes, I did get the answer, but I don't remember it. Scroll up in the
messages & replies and you should find the solution.

David H
(Bit tired at the moment)

[url]www.gatewayorlando.com[/url]

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!