SQL LIKE/REPLACE Syntax Issues 2

[TimothyP]

I am trying to run some code in SQL 2000 query analyzer.

I want to
SELECT all columns from the Publishers database
WHERE pubs_name is LIKE a supplied strSearch
(the string can be anywhere in the pubs_name)

When I do a syntax check on the code below I get the following SQL errors.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ', '.
Server: Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark before the character string '

'.

Here is the code I am trying to execute.

SELECT pubs_name FROM Publishers
WHERE pubs_name LIKE '%" & Replace(strSearch, "'", "''") & "%'

Can someone please point me in the right direction?

Thanks in advance for your time.

Tim

 

[gmmastros]

When writing t-sql code, you shouldn't use the quote symbol. You need to replace each quote with 2 single-quotes. Like this...

SELECT pubs_name FROM Publishers
WHERE pubs_name LIKE '%' & Replace(strSearch, ''''', '''''') & '%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

George some typos:

SELECT pubs_name FROM Publishers
WHERE pubs_name LIKE '%' [COLOR=red]+[/color] Replace(strSearch, [COLOR=red]''''[/color], '''''') [COLOR=red]+[/color] '%'
[code]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Thanks Boris. Now that you point it out... it's obvious. After posting, I also notices that strSearch doesn't appear to be a variable (because there is no @ symbol), so I'm guessing this is in-line sql from vb (or other programming language).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

And I point to typos w/o notice that strSearch is w/o @ ?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

when you are dynamically creating a bit of SQL Code like this, you should always test it by rpinting out the SQL generated first. You can almost always see exactly where the problem is by doing this.

Questions about posting. See faq183-874

 

[TimothyP]

Thanks gmmastros and bborissov!

Your replies were very helpful in pointing me in the right direction. It's been a while since I had my SQL hat on.

SQLSister - What do you mean by, "printing out the SQL generated first"? I searched the FAQ link you referenced but couldn't find what you meant by this.

Thanks again,
Tim

 

[SQLSister]

when you generate SQl dynamically you usually set it + to a variable
then use the Print comannd to print the contents of the variable. This way you will see the SQL statmenet that was dynamically generated and any problems with it will be generally very noticable

for instance

declare @sql varchar (1000)
declare @table1 varchar (50)
set @table1 = 'test'
set @sql = 'select * from' + @Table1
print @SQl

will print a statement that says:
select * fromtest

You can easily see from this print that the problem is you missed a space in creating the dynamic code.


Questions about posting. See faq183-874