MICROSOFT SQL SERVER
SQL query: That pesky apostrophe
SearchVB.com contributors
03.06.2001
Rating: -4.00- (out of 5)
Our tip about substituting for apostrophes in a SQL query provoked a
storm of responses. Nay, a veritable blizzard of responses. Many of
them took your tips guy to task for that bit of wisdom, but most of
them said that we should have just used the replace function instead.
So in the spirit of community building, we offer these folks' responses
as this week's tip.
This from Tom Johnston:
Bruce Wood offered a work around for updating database fields with
apostrophe's by substituting any chr$(39) with chr$(96) (backwards
apostrophe). Wouldn't you rather keep the characters you originally
typed in? SQL query errors can result from updating a field with either the
apostrophe or the quote (") symbol, depending on what your system is
using as a field delimiter. If your system is using apostrophes as a
field delimiter, apostrophes within the field will cause an error.
Likewise, if your system is using quotes as a field delimiter, quotes
within the field will also cause an error. So how did fields with
quotes & apostrophe's get in the database in the first place? SQL does
provide for this. Simply double up on any apostrophe found in the field
(if delimiting with apostrophe's), or double up any quote found (if
delimiting with quotes), prior to updating the field. For example, if
you are adding a text value to a database:
Joe's Stuff
The database tries to send 'Joe's Stuff' to the SQL server. The odd
number of apostrophes returns an error. But if you update it first to:
Joe's Stuff
Guess what? Updating the database now updates the field with the
original entry = Joe's Stuff
I've supplied a function below that will return an updating text
string, prepared for SQL update. It accepts two arguments, the
original string, and whatever your field delim character is
(quote=chr$(34) or apostrophe=chr$(39)). It returns a string that has
double of any found instance of the 2nd argument you provide...
To continue reading for free, register below or login
To read more you must become a member of SearchWinDevelopment.com
.
Not to be outdone, Christopher Nachand offers his approach:
In a SQL query, sometimes you cannot capture the rogue single apostrophe during data
entry. This is especially true when dealing with data transfer or
conversion applications, and it's usually found in people's last names.
You can use a fairly simple function to look for the single quote and
replace it with two single quotes, which most databases will recognize
as a single quote character for display purposes.
...a variation on the same theme.
Jesse Waldrip offers a slightly longer variation.
When saving info to a database table the apostrophe character causes
run-time errors. Most databases do not allow the use of the apostrophe
as it is interpreted as a new field starting. This will cause syntax
errors. Here is a very simple function that overcomes this problem by
handling it when data is saved to an Access or SQL Server database.
Greg Obleshchuk offers a solution based on the Replace function, as did
a number of readers.
Bruce Wood's tip about replacing the ' with a ` in a SQL query sounds good -- but it is
bad, very bad. The user would always try and turn the ` back into the
'. Most databases handle the ' very well. The code below could be
used instead. You could use this when you assign the textbox text to
the field. If you are using bound textboxes, place it in the
beforeupdate events. This tip will double '' and "" so all things will
work.
Another Replace tip, this from John Barone
You published a tip dealing with the apostrophe in a SQL query and suggested to
use the KeyPress event to replace the apostrophe with a backwards
apostrophe.
You can deal with it, more simply IMHO, by using the Replace function.
It doesn't even add an extra line (really); can't get easier than that!
'An example of how to use Replace
This tip is generated from SearchVB.com user Randy N. Ligsay:
Tip: the function below validates a SQL query string containing reserved
characters such as single quoted('), double quotes(""), etc., which
will cause runtime-error when saving it to database. To avoid such
problems, the function doubles that reserved character to override the
use of that character.
e.g. oracle backends
input : ILOVE'VBPROG - you can't save this to a database, because
oracle treats the single quotes as a string encloser, but you can save
this string without any change in value by using the function below.
Pass the string to the function, and the resulting output would be
ILOVE''VBPROG. This overrides the usage of the single quote for this
particular back end (the single quote was doubled). This function is
also able support other reserved character. Use the StrSearchString
param of the function.
Finally, this was the first response we got, the one that started this
magnum opus on its way to you. This is from Martin Gravel.
I received a tip&trick mail this morning. It was submitted by a fellow
named Bruce Wood, and was for fixing apostrophes in database fields.
His solution was to replace all apostrophes with the open quote
character. How can you guys publish such a nuisance? If a user needs an
apostrophe in a field, we, as programmers, have to let the user put an
apostrophe in the field and process it correctly! O'brian is not the
same as O`Brian
We asked Martin what his solution is for this type of SQL query, and here is his response.
The programmer should allow the quotes in the input textbox. Once he is
ready to send the data to the SQL server, the programmer should use the
REPLACE function to pre-process and thus hide special characters.
The REPLACE function will replace each occurrence of single quote by a
pair of single quotes. SQL will accept this syntax and when
encountering the single quotes pair, will drop one and include the
other in the string itself. You still need starting and ending single
quotes to enclose the whole string.
This method has been widely documented in different forums for several
months. Programmers use it more and more as it is very simple and
powerful.
Please read the tip that started it all, 'Darn that apostrophe'
|
|
|
|
|
|
