My String contains special character ' then how should it be fetched.

In my database there is a string which contains special character ' but when i tr开发者_运维技巧y to fetch it gives an error saying string termaination eg:

String temp=a'bc
SELECT * FROM table where name like 'temp%'

If you are calling this from client code (e.g. c#), you use a parameterized query:

string temp = "a'bc";
string sql = "SELECT * FROM table WHERE name LIKE @Name + '%'";

using (var cn = new SqlConnection(" connection string here "))
using (var cmd = new SqlCommand(sql, cn))
{
   cmd.Parameters.Add("@Name", SqlDbTypes.NVarChar, 50).Value = temp;

   cn.Open();
   using (var rdr = cmd.ExecuteReader())
   {

       //do something with your data reader
   }
}

You need to double up the single quote, like so: a''bc.

The entire query then becomes

SELECT * FROM table where name like 'a''bc%'

N.B. If the pattern in question is derived from user input, beware SQL injection attacks (also see xkcd).

In T-SQL you escape quotes in a quoted string by putting them twice, e.g.:

SELECT * FROM table where name like 'a''bc%'

You could also use a parametrised query instead of inline SQL

DECLARE @var NVARCHAR(50)
SET @var = ISNULL('%' + your_variable_here + '%', '') 

SELECT * FROM [dbo].[TableName] where [SomeColumn] like @var