CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2012sql-like
fc123
fc123

Reputation: 918

Single quotation inside string in sql

I have the following SQL query for searching purposes.

There is a @st variable and it is assigned a SQL query and at the end I will execute the query. The problem is the @st variable is assigned a string (which is query) and inside the string there is LIKE expression such as:

address like '%edmon%'

so it becomes:

set @st='select * from infoone where address like '%edmon%''

And it's causing an error:

incorrect syntax near edmon

The full query is following,

declare @v int;
declare @st varchar(1000);

set @st = 'select PLACEID, LEFT(CONVERT(NVARCHAR(1000), description), 500) as
des1, LOCATIONCITY, ADDRESS, DateT, RENT from infoone where '; 

Select @v = count(address) 
from infoone  
where ((address like '%main%') OR (address like '%main street%') 
       OR (address like '%main street edmonton%'));

if @v>0 
begin 
    set @st = @st + '(address like '%main%') OR 
                     (address like '%main street%') OR 
                     (address like '%main street edmonton%')' + 
                    'and ('; 
end 
else begin
    execute(@st)

Please let me know what I am doing wrong and how to resolve it

Thanks

Upvotes: 1

Views: 95

Answers (2)

M.Ali
M.Ali

Reputation: 69554

Gordon Linoff has explained you where you are going wrong with your single quotes ' but there are some other issues as well with your statement. a much better way of doing what you are trying to do would be something like this... 

declare @st nvarchar(MAX);

set @st=N' select PLACEID
                ,CONVERT(NVARCHAR(500),[description]) as des1
                ,LOCATIONCITY
                ,[ADDRESS]
                ,DateT
                ,RENT 
         from infoone  where 1 = 1 '; 

IF EXISTS (Select * from infoone  
                where(([ADDRESS] like '%main%') 
                   OR ([ADDRESS] like '%main street%') 
                   OR ([ADDRESS] like '%main street edmonton%')))
 begin 
   set @st = @st+ N' AND (
                       ([ADDRESS] like ''%main%'') 
                       OR
                       ([ADDRESS] like ''%main street%'') 
                       OR
                       ([ADDRESS] like ''%main street edmonton%'')
                      )'; 
 end 
else 
  begin
    set @st = @st+ N' AND  (Some Other Condition) )'
  end

EXECUTE sp_executesql @st

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

To get a single quote on a string, use '':

set @st = 'select * from infoone where address like ''%edmon%'''

Upvotes: 4

Related Questions