CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Prasad
Prasad

Reputation: 65

How to search for special characters in SQL Server 2008 R2?

I got the requirements that I have to search for special characters like the names who are having single quotes in their names c'te

ex: 

select * from names where name like 'c'test%'

For this I got the solution like 

select * from names where name like '%''%'

The above answer is useful to search who are having single quotes in their names but my requirement is how do i search the name directly like 

select * from names where name like 'c'test%'

because from application side they will search for the lable last name d'tech

Please note that we should not pass any extra quotes while searching the names

Help on this with example will be appreciated

Thanks in advance

Upvotes: 0

Views: 1092

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

You just need to double them up (I don't know what "should not pass any extra quotes" means but this is how you do it):

select column1,column2 -- don't use *
from dbo.names -- always use schema prefix
where name like 'c''test%'; -- terminate with semi-colon

However if you use properly parameterized statements you shouldn't need to worry about it. You only need to worry about escaping single quotes if you are building your query dynamically, which you shouldn't be doing because you lose strong typing and open yourself up to SQL injection. Quick example:

string sql = "SELECT * FROM names WHERE name LIKE @Name;";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
  cmd.Parameters.Add(new SqlParameter("Name", "c'test"));
  ...
}

Upvotes: 1

Related Questions