SQL query search ampersand &

Question

Need to write a SQL query to search special character in a column. Text needed to search is 'R&D' but issue is that SQL server 2005 is taking it as a logical operator.

Any help?

Answer 1

Select * From dbo.MyTable
Where MyColumn like '%R&D%'

or you could use this instead of like:

Select * From dbo.MyTable
Where PatIndex('%R&D%', MyColumn) > 0

Answer 2

declare @tbl table
(
    textcol varchar(50)
)

insert into @tbl select 'R&D'
insert into @tbl select 'What is R&D'
insert into @tbl select 'SometextR&DAndsometext'
insert into @tbl select 'This Row will not return'

select * from @tbl where textcol like '%R&D%'

Answer 3

I guess you are using parameters, correct?
Because if you do NOT use parameters, it works (see the other answers).

What doesn't work is this:

declare @tmp nvarchar(20)
set @tmp = 'R&D'

select * from MyTable where MyColumn = @tmp

The only way that I found to make it work with parameters is doing it like this:

declare @tmp nvarchar(20)
set @tmp = 'R&D'

select * from MyTable where MyColumn like '%' + @tmp + '%'

---

EDIT:

First of all, I need to know if you're using parameters or not.

If not, I don't understand the problem at all.
One of these will work (with or without LIKE):

select * from MyTable where MyColumn = 'R&D'
select * from MyTable where MyColumn like '%R&D%'

If you do work with parameters, my above solution ( ... like '%' + @tmp + '%') is the only one that I know of.

---

EDIT 2

Now that I know that the data type is "text": The problem is that you can only search text with LIKE (not with '=') and you can't index a text column to increase speed.
The text data type is deprecated, so you should change it into varchar(max) anyway.
And varchar(max) doesn't have the limitations that text has: you can search it with '=' and you can index it.

So that would be the correct solution: change it from text to varchar(max) and everything will work!

Answer 4

If you are using Contains() then wrapping double speechmarks around the search term might help

select Column_Name from Table_Name where contains(Column_Name,'"R&D"')