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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Travis McGee - 29 Jun 2008 03:47 GMT

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???

Reply to this Message

Dan Guzman - 29 Jun 2008 04:06 GMT

> Say the field has ..... I love my @*&!& friend

CREATE TABLE dbo.myTable
(
myField varchar(30)
)
INSERT INTO dbo.myTable(myField)
VALUES ('I love my @*&!& friend')
GO

> 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

I get the results below for all 3 queries:

myField
------------------------------
I love my @*&!& friend

There is nothing special about '@' here that comes to mind. You might try
qualifying the table with the schema name. Perhaps there are multiples
table with the same name but in different schema.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Does anybody know why searching for a string '@' with LIKE does not work
> even when you use ESCAPE
[quoted text clipped - 12 lines]
>
> Any help???

Reply to this Message

Michael Coles - 29 Jun 2008 07:46 GMT

See response in .datawarehouse. Please don't multipost.

Signature

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

> Does anybody know why searching for a string '@' with LIKE does not work
> even when you use ESCAPE
[quoted text clipped - 12 lines]
>
> Any help???

Reply to this Message