RE: SQL Queries on Irish last names that have an apostrophe
Did you try '#PreserveSingleQuotes(Evaluate(ThisField))#%' -Original Message- From: John McKown [mailto:[EMAIL PROTECTED]] Sent: Friday, December 22, 2000 9:51 AM To: CF-Talk Subject: RE: SQL Queries on Irish last names that have an apostrophe That sounds cool. I will check out the indexing features of SQL 7 since that is what I am working on. In the mean time, here is how I fixed it (notice the ReReplace): SELECT * FROM #MemberTable# WHERE 0 = 0 AND #ThisField# LIKE '%#REReplace(Evaluate(ThisField), "'", "''", "ALL")#%' John McKown, VP Business Services Delaware.Net, Inc. 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 email: [EMAIL PROTECTED] phone: 302-736-5515 fax: 302-736-5945 icq: 1495432 > -Original Message- > From: paul smith [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 22, 2000 12:38 PM > To: CF-Talk > Subject: Re: SQL Queries on Irish last names that have an apostrophe > > > SQL7 Full-text search handles this automagically. > That is, searches for O'Neill without any massaging > of O'Neill to O''Neill, and so forth. > > Perhaps Verity does, too. But Verity doesn't > allow standard SQL in the same breath as SQL7 > full-text search does. Perhaps the new Verity > K2 will add amazing new functionality. > > best, paul > > At 11:48 AM 12/22/00 -0500, you wrote: > >I am not sure why someone would want an apostrophe in their last name, :) > >but I need to know how to get around that when writing SQL queries > >that search records for last names contain apostrophes. > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Queries on Irish last names that have an apostrophe
Hmm, what database are you using? W. Troy Leaver Systems Development Consultant Solution Specialists, Inc. 888-668-9193 -- Original Message -- From: "John McKown" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Fri, 22 Dec 2000 12:21:02 -0500 >Yes, that was a typo. The % is actually within the quotes. I tried using '' to escape the last name and I still get an error. I also tried the PreserveSingleQuotes to no avail. Ugh. John McKown, VP Business Services Delaware.Net, Inc. 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 email: [EMAIL PROTECTED] phone: 302-736-5515 fax: 302-736-5945 icq: 1495432 > -Original Message- > From: Troy Leaver [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 22, 2000 11:51 AM > To: CF-Talk > Subject: Re: SQL Queries on Irish last names that have an apostrophe > > > With most SQL databases, you can get around this by escaping the > apostrophe with another apostrophe, so that your query reads: > > SELECT * > FROM Lawyers > WHERE 0 = 0 > AND LNAME LIKE 'o''donnell'% > > I also noticed that your wildcard is outside your string > delimiters. I'm assuming that's a typo in your post; it would > cause a problem too :). > > W. Troy Leaver > Systems Development Consultant > Solution Specialists, Inc. > 888-668-9193 > > -- Original Message -- > From: "John McKown" <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Fri, 22 Dec 2000 11:48:19 -0500 > > >I hope this is an easy question for you SQL gurus. > > I am not sure why someone would want an apostrophe in their last name, :) > but I need to know how to get around that when writing SQL queries > that search records for last names contain apostrophes. > > Here is a sample query that loops through the search form > fields and builds a dynamic query: > > > SELECT * > FROM #MemberTable# > WHERE 0 = 0 > > > AND #ThisField# LIKE '#Evaluate(ThisField)#%' > > > > > > And here is what gets rendered when we pass some Irish name: > > SELECT * > FROM Lawyers > WHERE 0 = 0 > AND LNAME LIKE 'o'donnell'% > > > And of course, this produces a SQL error. > Anyone have tips for getting around this and still being able > to search for names that contain an apostrophe? > > Thanks in advance. > > John McKown, VP Business Services > Delaware.Net, Inc. > 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 > email: [EMAIL PROTECTED] > phone: 302-736-5515 > fax: 302-736-5945 > icq: 1495432 > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Queries on Irish last names that have an apostrophe
SQL7 Full-text search handles this automagically. That is, searches for O'Neill without any massaging of O'Neill to O''Neill, and so forth. Perhaps Verity does, too. But Verity doesn't allow standard SQL in the same breath as SQL7 full-text search does. Perhaps the new Verity K2 will add amazing new functionality. best, paul At 11:48 AM 12/22/00 -0500, you wrote: >I am not sure why someone would want an apostrophe in their last name, :) >but I need to know how to get around that when writing SQL queries >that search records for last names contain apostrophes. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Queries on Irish last names that have an apostrophe
Yes, that was a typo. The % is actually within the quotes. I tried using '' to escape the last name and I still get an error. I also tried the PreserveSingleQuotes to no avail. Ugh. John McKown, VP Business Services Delaware.Net, Inc. 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 email: [EMAIL PROTECTED] phone: 302-736-5515 fax: 302-736-5945 icq: 1495432 > -Original Message- > From: Troy Leaver [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 22, 2000 11:51 AM > To: CF-Talk > Subject: Re: SQL Queries on Irish last names that have an apostrophe > > > With most SQL databases, you can get around this by escaping the > apostrophe with another apostrophe, so that your query reads: > > SELECT * > FROM Lawyers > WHERE 0 = 0 > AND LNAME LIKE 'o''donnell'% > > I also noticed that your wildcard is outside your string > delimiters. I'm assuming that's a typo in your post; it would > cause a problem too :). > > W. Troy Leaver > Systems Development Consultant > Solution Specialists, Inc. > 888-668-9193 > > -- Original Message -- > From: "John McKown" <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Fri, 22 Dec 2000 11:48:19 -0500 > > >I hope this is an easy question for you SQL gurus. > > I am not sure why someone would want an apostrophe in their last name, :) > but I need to know how to get around that when writing SQL queries > that search records for last names contain apostrophes. > > Here is a sample query that loops through the search form > fields and builds a dynamic query: > > > SELECT * > FROM #MemberTable# > WHERE 0 = 0 > > > AND #ThisField# LIKE '#Evaluate(ThisField)#%' > > > > > > And here is what gets rendered when we pass some Irish name: > > SELECT * > FROM Lawyers > WHERE 0 = 0 > AND LNAME LIKE 'o'donnell'% > > > And of course, this produces a SQL error. > Anyone have tips for getting around this and still being able > to search for names that contain an apostrophe? > > Thanks in advance. > > John McKown, VP Business Services > Delaware.Net, Inc. > 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 > email: [EMAIL PROTECTED] > phone: 302-736-5515 > fax: 302-736-5945 > icq: 1495432 > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Queries on Irish last names that have an apostrophe
With most SQL databases, you can get around this by escaping the apostrophe with another apostrophe, so that your query reads: SELECT * FROM Lawyers WHERE 0 = 0 AND LNAME LIKE 'o''donnell'% I also noticed that your wildcard is outside your string delimiters. I'm assuming that's a typo in your post; it would cause a problem too :). W. Troy Leaver Systems Development Consultant Solution Specialists, Inc. 888-668-9193 -- Original Message -- From: "John McKown" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Fri, 22 Dec 2000 11:48:19 -0500 >I hope this is an easy question for you SQL gurus. I am not sure why someone would want an apostrophe in their last name, :) but I need to know how to get around that when writing SQL queries that search records for last names contain apostrophes. Here is a sample query that loops through the search form fields and builds a dynamic query: SELECT * FROM #MemberTable# WHERE 0 = 0 AND #ThisField# LIKE '#Evaluate(ThisField)#%' And here is what gets rendered when we pass some Irish name: SELECT * FROM Lawyers WHERE 0 = 0 AND LNAME LIKE 'o'donnell'% And of course, this produces a SQL error. Anyone have tips for getting around this and still being able to search for names that contain an apostrophe? Thanks in advance. John McKown, VP Business Services Delaware.Net, Inc. 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 email: [EMAIL PROTECTED] phone: 302-736-5515 fax: 302-736-5945 icq: 1495432 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Queries on Irish last names that have an apostrophe
Use the CF function PreserveSingleQuotes() to encapsulate the last name. -Original Message- From: John McKown [mailto:[EMAIL PROTECTED]] Sent: Friday, December 22, 2000 8:48 AM To: CF-Talk Subject: SQL Queries on Irish last names that have an apostrophe I hope this is an easy question for you SQL gurus. I am not sure why someone would want an apostrophe in their last name, :) but I need to know how to get around that when writing SQL queries that search records for last names contain apostrophes. Here is a sample query that loops through the search form fields and builds a dynamic query: SELECT * FROM #MemberTable# WHERE 0 = 0 AND #ThisField# LIKE '#Evaluate(ThisField)#%' And here is what gets rendered when we pass some Irish name: SELECT * FROM Lawyers WHERE 0 = 0 AND LNAME LIKE 'o'donnell'% And of course, this produces a SQL error. Anyone have tips for getting around this and still being able to search for names that contain an apostrophe? Thanks in advance. John McKown, VP Business Services Delaware.Net, Inc. 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 email: [EMAIL PROTECTED] phone: 302-736-5515 fax: 302-736-5945 icq: 1495432 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Queries on Irish last names that have an apostrophe
I hope this is an easy question for you SQL gurus. I am not sure why someone would want an apostrophe in their last name, :) but I need to know how to get around that when writing SQL queries that search records for last names contain apostrophes. Here is a sample query that loops through the search form fields and builds a dynamic query: SELECT * FROM #MemberTable# WHERE 0 = 0 AND #ThisField# LIKE '#Evaluate(ThisField)#%' And here is what gets rendered when we pass some Irish name: SELECT * FROM Lawyers WHERE 0 = 0 AND LNAME LIKE 'o'donnell'% And of course, this produces a SQL error. Anyone have tips for getting around this and still being able to search for names that contain an apostrophe? Thanks in advance. John McKown, VP Business Services Delaware.Net, Inc. 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 email: [EMAIL PROTECTED] phone: 302-736-5515 fax: 302-736-5945 icq: 1495432 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
