Using the Replace function within a SQL query produces double quotes, suggestions?

Dev Shed Forums Sponsor:

February 25th, 2005, 11:25 AM

spyordie007

Registered User

Join Date: Feb 2005

Posts: 7

Time spent in forums: 2 h 4 m 4 sec
Reputation Power: 0

Using the Replace function within a SQL query produces double quotes, suggestions?

Hi everyone, long time lurker but first time poster. I'm having an issue with a CFML script I'm working on that I'm hoping someone can offer suggestions.

I'm using the replace function inside of a cfquery (to MS SQL) so that I can have a search performed for multiple keywords. Here is an excerpt of the SQL query (within cfquery) from the code I'm using:

Code:

WHERE column LIKE '%#Replace(Trim(search string), " ", "%' AND column  LIKE '%", "ALL")#%'

So if my search string was "1 2 3" what I would expect it to return would be:

Code:

WHERE colum LIKE '%1%' AND colum LIKE '%2%' AND colum LIKE '%3%'

However the problem is that the string that is getting passed to my SQL server is:

Code:

WHERE colum LIKE '%1%'' AND colum LIKE ''%2%'' AND colum LIKE ''%3%'

(inserting "''" rather than "'" like it should)

There is one catch and that is that the server this script I'm working on is running under BlueDragon (not Macromedia's CF server). In looking at the code it would seem pretty straight forward, however it's also possible that I'm looking at a BD bug. If anyone could verify this (by trying the same thing on MM's CF) or have suggestions for other ways to do this it would be much appreciated.

Thanks in advance,

-Erik

February 25th, 2005, 11:30 AM

spyordie007

Registered User

Join Date: Feb 2005

Posts: 7

Time spent in forums: 2 h 4 m 4 sec
Reputation Power: 0

I forgot to add that I did try just sticking that code inside of a cfoutput to verify that it was parsing correctly:

Code:

<cfoutput>#Replace(Trim(search string), " ", "%' AND column  LIKE '%", "ALL")#</cfoutput>

And that does replace the quotes like it's supposed to.

February 25th, 2005, 12:43 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,682

Time spent in forums: 1 Week 4 Days 15 h 25 m 55 sec
Reputation Power: 53

What if you have CF output it exactly as you are using it in the SQL, like this:

Code:

<cfoutput>'%#Replace(Trim(search string), " ", "%' AND column  LIKE '%", "ALL")#%'</cfoutput>

Where you call the function shouldn't make any difference, so if it's putting the extra quotes in I'd make sure it doesn't have anything to do with the quotes you have surrounding the function call.

Comments on this post

spyordie007 disagrees: Helpful comments!

__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian.
How to Post a Question in the Forums

February 25th, 2005, 01:00 PM

spyordie007

Registered User

Join Date: Feb 2005

Posts: 7

Time spent in forums: 2 h 4 m 4 sec
Reputation Power: 0

Thanks for the response.

Code:

<cfoutput>'%#Replace(Trim("search string"), " ", "%' AND column  LIKE '%", "ALL")#%'</cfoutput>

Returns:
'%search%' AND column LIKE '%string%'

February 25th, 2005, 01:11 PM

spyordie007

Registered User

Join Date: Feb 2005

Posts: 7

Time spent in forums: 2 h 4 m 4 sec
Reputation Power: 0

For what it's worth I've tried manually specifying the search string in the query:

Code:

WHERE column LIKE '%#Replace(Trim("my search string"), " ", "%' AND column LIKE '%", "ALL")#%'

When I do this the string that gets passed to my SQL server is:

Code:

WHERE column LIKE '%my%'' AND column LIKE ''%search%'' AND column LIKE ''%string%'

February 25th, 2005, 02:28 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,682

Time spent in forums: 1 Week 4 Days 15 h 25 m 55 sec
Reputation Power: 53

Can you post the <cfquery> block and the actual variable value that you are passing into the replace function? I'll try to test it locally. I'm still fairly sure the problem must be in the context that the replace function is being used, because again, using replace inside a CFQUERY block should make no difference (CF doesn't care at all where you call the function).

February 28th, 2005, 01:57 PM

spyordie007

Registered User

Join Date: Feb 2005

Posts: 7

Time spent in forums: 2 h 4 m 4 sec
Reputation Power: 0

Sorry it's taken so long for me to reply. I hadnt posted my entire query block earlier just because of how long it is however as requested:

Code:

	<cfquery datasource="ims" dbtype="odbc" name="qkb">
		SELECT SC_KB.article, SC_KB.title, SC_KB.keywords, SC_KB.TechID, SC_KB.accesslevel<cfif #form.boundry# eq 'b'>, SC_KB.content</cfif>, Techs.TechID, Techs.FirstName, Techs.LastName
		FROM SC_KB
		INNER JOIN Techs
		ON SC_KB.TechID=Techs.TechID
		WHERE SC_KB.accesslevel <= '#userlevel#'
		AND SC_KB.keywords LIKE '%#Replace(Trim(form.terms), " ", "%' AND SC_KB.title  LIKE '%", "ALL")#%'
		OR SC_KB.accesslevel <= '#userlevel#'
		AND SC_KB.title LIKE '%#Replace(Trim("my search string"), " ", "%' AND SC_KB.title  LIKE '%", "ALL")#%'
		<cfif #form.boundry# eq 'b'>OR SC_KB.accesslevel <= '#userlevel#'
		AND SC_KB.content LIKE '%#Replace(Trim(form.terms), " ", "%' AND SC_KB.content  LIKE '%", "ALL")#%'</cfif>
	</cfquery>

As you can see it's running the search based on input from a form (form.terms). I've also tried manually specifiying the terms on the title search ("my search string"). Regardless of how I supply the term(s) to search for the output is still:
AND SC_KB.title LIKE '%my%'' AND SC_KB.title LIKE ''%search%'' AND SC_KB.title LIKE ''%terms%'

I'm eager to see if you are able to replicate this issue.

Thanks again,

Erik

February 28th, 2005, 02:34 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,682

Time spent in forums: 1 Week 4 Days 15 h 25 m 55 sec
Reputation Power: 53

I think I'm not able to replicate the problem...I ran this code:

Code:

<cfset form.terms = "term1 term2 term3"/>
<cfset form.boundry = "boundary" />
<cfset userLevel = "userLevel" />
<cfoutput>

cfquery datasource="ims" dbtype="odbc" name="qkb"<br>
SELECT SC_KB.article, SC_KB.title, SC_KB.keywords, SC_KB.TechID, 

SC_KB.accesslevel<cfif #form.boundry# eq 'b'>, SC_KB.content</cfif>, 

Techs.TechID, Techs.FirstName, Techs.LastName<br>
FROM SC_KB<br>
INNER JOIN Techs<br>
ON SC_KB.TechID=Techs.TechID<br>
WHERE SC_KB.accesslevel <= '#userlevel#'<br>
AND SC_KB.keywords LIKE '%#Replace(Trim(form.terms), " ", "%' <br>
AND SC_KB.title  LIKE '%", "ALL")#%'<br>
OR SC_KB.accesslevel <= '#userlevel#'<br>
AND SC_KB.title LIKE '%#Replace(Trim("my search string"), " ", "%'<br> 
AND SC_KB.title  LIKE '%", "ALL")#%'<br>
cfif #form.boundry# eq 'b'<br>
OR SC_KB.accesslevel <= '#userlevel#'<br>
AND SC_KB.content LIKE '%#Replace(Trim(form.terms), " ", "%'<br> 
AND SC_KB.content  LIKE '%", "ALL")#%'<br>
/cfif<br>
/cfquery<br>

</cfoutput>

and got this output (I see no double quotes):

cfquery datasource="ims" dbtype="odbc" name="qkb"
SELECT SC_KB.article, SC_KB.title, SC_KB.keywords, SC_KB.TechID, SC_KB.accesslevel, Techs.TechID, Techs.FirstName, Techs.LastName
FROM SC_KB
INNER JOIN Techs
ON SC_KB.TechID=Techs.TechID
WHERE SC_KB.accesslevel <= 'userLevel'
AND SC_KB.keywords LIKE '%term1%'
AND SC_KB.title LIKE '%term2%'
AND SC_KB.title LIKE '%term3%'
OR SC_KB.accesslevel <= 'userLevel'
AND SC_KB.title LIKE '%my%'
AND SC_KB.title LIKE '%search%'
AND SC_KB.title LIKE '%string%'
cfif boundary eq 'b'
OR SC_KB.accesslevel <= 'userLevel'
AND SC_KB.content LIKE '%term1%'
AND SC_KB.content LIKE '%term2%'
AND SC_KB.content LIKE '%term3%'
/cfif
/cfquery

Any ideas? What database driver are you using? MySQL? Oracle? etc.

March 1st, 2005, 08:54 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,919

Time spent in forums: 1 Month 4 Weeks 12 h 51 m 6 sec
Reputation Power: 1018

would using PreserveSingleQuotes() help?

Comments on this post

spyordie007 disagrees: Helpful comments!

__________________
r937.com | rudy.ca

#10

March 1st, 2005, 09:40 AM

VelvettFogg

Contributing User

Join Date: Dec 2004

Posts: 34

Time spent in forums: 3 h 13 m 46 sec
Reputation Power: 4

I've had similar trouble before. I solved it like this;

<cfscript>
spaceBar = " ";
andColumnLike = "%' AND column LIKE '%";
queryString = "%#Replace(Trim(search string), spaceBar, andColumnLike, "ALL")#%";
</cfscript>

Then in your query use something like this;

WHERE column LIKE '#queryString#'

I cannot tell you why, but when I've done the replace like that, with variables instead of the actual text characters, I've been able to over come the single quote glitch you've described.

(code was not tested)

Luck

Comments on this post

spyordie007 disagrees!

#11

March 4th, 2005, 06:46 PM

spyordie007

Registered User

Join Date: Feb 2005

Posts: 7

Time spent in forums: 2 h 4 m 4 sec
Reputation Power: 0

Thanks everyone for all the responses.

I was finally able to resolve this using r937's suggestion of preservesinglequotes():
Prior to my query:

Code:

<cfset searchterms = "#Replace(Trim(form.terms), " ", "%' AND SC_KB.keywords LIKE '%", "ALL")#">

And in the query:

Code:

AND SC_KB.keywords LIKE '%#PreserveSingleQuotes(searchterms)#%'

I really appreciate all the suggestions. Thanks again!

-Erik

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: