CODEWITHSUNDEEP
sql-server-2005
user867502
user867502

Reputation: 1

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 try to fetch it gives an error saying string termaination eg:

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

Upvotes: 0

Views: 537

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

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

Upvotes: 1

Kane
Kane

Reputation: 16802

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

Upvotes: 0

cjk
cjk

Reputation: 46425

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

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

Upvotes: 0

NPE
NPE

Reputation: 500367

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).

Upvotes: 0

Related Questions