TSQL - LIKE search statement for a '%@%'

TSQL - LIKE search statement for a '%@%'

Does anybody know why searching for a string '@' with LIKE does not work 
even when you use ESCAPE

Very strange......

Say the field has .....   I love my @*&!& friend
And I want to SELECT this row when I search for @ with LIKE
Select * from myTable where myField like '%@%'     does not work
Select * from myTable where myField like '%#@%'  escape '#'     does not 
work either
Select * from myTable where myField like '%' + char(64) + '%'     does not 
work either

What a pain in the something search + Why me

Any help???

date: Sat, 28 Jun 2008 22:47:26 -0400 author: Travis McGee

Re: TSQL - LIKE search statement for a '%@%'

Works fine over here.  Does the column you're searching actually contain the 
@ symbol?  Are you sure you have the correct column in your WHERE clause? 
Try this and see if it doesn't work for you:

CREATE TABLE #Test
(
  Id INT NOT NULL PRIMARY KEY,
  EmailAddress VARCHAR(100) NOT NULL
);

INSERT INTO #Test
(
  Id,
  EmailAddress
)
SELECT
  1,
  'st_peter@heaven.com'
UNION
SELECT
  2,
  'tiger.woods@golfusa.com'
UNION
SELECT
  3,
  'invalid_email';

SELECT *
FROM #Test
WHERE EmailAddress LIKE '%@%';

DROP TABLE #Test;

/*
My results:
1,st_peter@heaven.com
2,tiger.woods@golfusa.com
*/
-- 

========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/


"Travis McGee"  wrote in message 
news:eeZv6JZ2IHA.4672@TK2MSFTNGP04.phx.gbl...
> Does anybody know why searching for a string '@' with LIKE does not work 
> even when you use ESCAPE
>
> Very strange......
>
> Say the field has .....   I love my @*&!& friend
> And I want to SELECT this row when I search for @ with LIKE
> Select * from myTable where myField like '%@%'     does not work
> Select * from myTable where myField like '%#@%'  escape '#'     does not 
> work either
> Select * from myTable where myField like '%' + char(64) + '%'     does not 
> work either
>
> What a pain in the something search + Why me
>
> Any help???

date: Sun, 29 Jun 2008 02:44:19 -0400 author: Michael Coles michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net

Re: TSQL - LIKE search statement for a '%@%'

What are we going to do without the Newsgroups and the dedicated user like 
you.
Of course, you reminded me that I wrote hundreds of queries against parsing 
eMails addresses with LIKE '%@%'.

With a quick look at the data with ascii(single character) it turned out to 
be that the @ characters in the data were not our good old friend char(64) = 
@ but they were char(174) and looks like regular @ in the query analyzer, 
etc.

Bummer.....has cost me a good 1-2 hours of frustration.


"Michael Coles" <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote in 
message news:OVq5QOb2IHA.3920@TK2MSFTNGP02.phx.gbl...
> Works fine over here.  Does the column you're searching actually contain 
> the @ symbol?  Are you sure you have the correct column in your WHERE 
> clause? Try this and see if it doesn't work for you:
>
> CREATE TABLE #Test
> (
>  Id INT NOT NULL PRIMARY KEY,
>  EmailAddress VARCHAR(100) NOT NULL
> );
>
> INSERT INTO #Test
> (
>  Id,
>  EmailAddress
> )
> SELECT
>  1,
>  'st_peter@heaven.com'
> UNION
> SELECT
>  2,
>  'tiger.woods@golfusa.com'
> UNION
> SELECT
>  3,
>  'invalid_email';
>
> SELECT *
> FROM #Test
> WHERE EmailAddress LIKE '%@%';
>
> DROP TABLE #Test;
>
> /*
> My results:
> 1,st_peter@heaven.com
> 2,tiger.woods@golfusa.com
> */
> -- 
>
> ========
> Michael Coles
> "Pro SQL Server 2008 XML"
> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>
>
> "Travis McGee"  wrote in message 
> news:eeZv6JZ2IHA.4672@TK2MSFTNGP04.phx.gbl...
>> Does anybody know why searching for a string '@' with LIKE does not work 
>> even when you use ESCAPE
>>
>> Very strange......
>>
>> Say the field has .....   I love my @*&!& friend
>> And I want to SELECT this row when I search for @ with LIKE
>> Select * from myTable where myField like '%@%'     does not work
>> Select * from myTable where myField like '%#@%'  escape '#'     does not 
>> work either
>> Select * from myTable where myField like '%' + char(64) + '%'     does 
>> not work either
>>
>> What a pain in the something search + Why me
>>
>> Any help???
>
>

date: Sun, 29 Jun 2008 11:02:32 -0400 author: Travis McGee