Escaping a comma in a LIKE statement

A simple question I'm sure...
I am having problems with the following statement:
Select * from data where
Company LIKE '%HEI,%'
It appears that the comma is being interpreted as something other than a nor
mal string value. When I remove the comma everything works fine. Is there a
way to escape the comma? I've spent a great deal of time looking for the ans
wer, apparently I am not lo
oking in the right places. Any help would be appreciated.Can you be a bit more specific, or show us a repro? Below statement work jus
t like expected, i.e., the comma
does not get any special treatment:
SELECT * FROM
(
SELECT 'HEL,oo' AS x
UNION
SELECT 'HELOO' AS x
) AS d
WHERE x LIKE 'HEL,%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> A simple question I'm sure...
> I am having problems with the following statement:
> Select * from data where
> Company LIKE '%HEI,%'
> It appears that the comma is being interpreted as something other than a normal st
ring value. When I remove
the comma everything works fine. Is there a way to escape the comma? I've sp
ent a great deal of time looking
for the answer, apparently I am not looking in the right places. Any help wo
uld be appreciated.|||Sure. Here's the full query and the resulting error message:
Query:
Select * From data
WHERE Company like '%HEI %',
And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Here's the error message:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near ','.
"Tibor Karaszi" wrote:

> Can you be a bit more specific, or show us a repro? Below statement work j
ust like expected, i.e., the comma
> does not get any special treatment:
> SELECT * FROM
> (
> SELECT 'HEL,oo' AS x
> UNION
> SELECT 'HELOO' AS x
> ) AS d
> WHERE x LIKE 'HEL,%'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
> news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> the comma everything works fine. Is there a way to escape the comma? I've
spent a great deal of time looking
> for the answer, apparently I am not looking in the right places. Any help
would be appreciated.
>
>|||I see the problem. Don't know how I missed the comma. Thanks anyway
"MSchlaud" wrote:
[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorre
ct syntax near ','.
>
> "Tibor Karaszi" wrote:
>|||On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:

>Sure. Here's the full query and the resulting error message:
>Query:
>Select * From data
>WHERE Company like '%HEI %',
>And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
>Here's the error message:
>ODBC Error Code = 37000 (Syntax error or access violation)
>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrec
t syntax near ','.
>
Hi MSchlaud,
You have the comma outside of the apostrophe.
In your original question, you said Company LIKE '%HEI,%' (with the comma
as part of the LIKE argument).
In the above, you have Company LIKE '%HEI% ', (with a space between the
apostrophes and a comma after the closing apoostrophe)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm sorry but that is not something I can execute as you didn't post the CRE
ATE TABLE and INSERT statements.
I.e., I cannot try to reproduce that error message.
However, trying to parse only give me that you have a comma *after* the stri
ng definition (outside the
string), before the word AND. Try below:
SELECT * FROM data
WHERE Company LIKE '%HEI %'
AND RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:B2396540-2022-4711-A1F2-331F46BD1DC2@.microsoft.com...[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorre
ct syntax near ','.
>
> "Tibor Karaszi" wrote:
>
comma[vbcol=seagreen]
remove[vbcol=seagreen]
looking[vbcol=seagreen]|||Yes, I see that now. Thanks for the quick responses.
"Hugo Kornelis" wrote:

> On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:
>
> Hi MSchlaud,
> You have the comma outside of the apostrophe.
> In your original question, you said Company LIKE '%HEI,%' (with the comma
> as part of the LIKE argument).
> In the above, you have Company LIKE '%HEI% ', (with a space between the
> apostrophes and a comma after the closing apoostrophe)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>