I'm not completely clear on what your question is: it sounds as if you are
concerned only with correcting the bad data and not with how it got there in
the first place. Frankly, I think you'll need to deal with both to fully
resolve the problem; there's no point in fixing the existing bad data
manually if the input procedures continue to allow bad data to be created in
your tables. Unfortunately, you haven't said anything about how you are
doing the input so this reply is going to get a bit wordy ;-)
There are many different techniques used for both inputting and correcting
data. In the case of very small amounts of rows, say a few dozen, you might
simply create the data using INSERT statements that you issue from the
command line or, more likely, write in a script and execute via db2 -tvf
<filename>. In both of those cases, you handle apostrophes by doubling them,
e.g. insert into table1 (id, name, salary) values(1, 'O''Brien', 50000).
For larger volumes of data, you will often use the IMPORT or LOAD command
to, in effect, copy the data from a flat file into DB2. The IMPORT and LOAD
commands have a MODIFIED BY clause that takes a number of arguments that
affect the behaviour of the command. For example, for ASCII files that are
being imported, the 'chardel' modifier can use the character of your choice
as the string delimiter; as long as you choose something other than an
apostrophe, apostrophes in a name like O'Brien should be handled properly.
If you are writing your own programs to load your data, I would suggest
using parameter markers in your insert statements; they will handle the
apostrophes for you, very cleanly in my experience. For instance, in a Java
program, something like this would work quite well and wouldn't have any
problems with names containing apostrophes:
----------------------------------------------------------------------------
---------------------------------
String insertSQL =
"insert into personnel " +
"(id, name, salary) " +
"values(?, ?, ?)";
PreparedStateme nt pstmt02 = null;
try {
pstmt02 = conn01.prepareS tatement(insert SQL);
}
catch (SQLException sql_excp) { //error handling omitted }
int numRowsInserted = 0;
try {
pstmt02.setInt( 1, 1);
pstmt02.setStri ng(2, "O'Brien");
pstmt02.setBigD ecimal(3, 50000.00);
numRowsUpdated = pstmt02.execute Update();
}
catch (SQLException sql_excp) { //error handling omitted }
----------------------------------------------------------------------------
---
With respect to your at symbols (@) being interpreted as apostrophes, I'm
not sure what is causing that since I'm not sure how you're inputting the
data. It certainly isn't normal behaviour for DB2 to convert @ to ' on the
fly. My best guess is that you are using a DB2 script that uses @ as the
statement delimiter and this has somehow gotten misinterpreted as a string
delimiter, probably because of user error in setting the delimiters. The
other prime suspect is that you may have a code page issue where the code
page you are using doesn't have the @ symbol in it and apostrophe is being
stored instead. I'm afraid I don't have much experience with code pages
though so I can't suggest how you can prove that, let alone fix it.
As far as changing apostrophes back to @ symbols, that should be pretty
easy. Here is a short script that creates a row which mistakenly contains an
apostrophe instead of an @ symbol:
----------------------------------------------------------------------------
-----------------------
connect to sample;
drop table temp01;
create table temp01
(id smallint not null,
name char(20) not null,
salary dec(9,2) not null,
email char(50) not null,
primary key(id));
Insert into temp01 (id, name, salary, email) values (1, 'O''Brien',
50000.00, 'Seamus''hotmai l.com');
select * from temp01;
----------------------------------------------------------------------------
------------------------------------
To repair the data, I would recommend doing the update on the basis of the
primary key since, by definition, it always refers to exactly one row so
that you can't inadvertently update any more than one row:
--------------------------------------------
update temp01
set email = 'S*****@hotmail .com'
where id = 1;
---------------------------------------------
select * from temp01;
However, the following would also work:
-----------------------------------------
update temp01
set email = 'S*****@hotmail .com'
where email = 'Seamus''hotmai l.com';
--------------------------------------------
The difference is that the second statement would change *every* occurrence
of Seamus'hotmail. com to
Se****@hotmail. com.
I hope this helps.
Rhino
"James Foreman" <ja***********@ abcmail.co.uk> wrote in message
news:78******** *************** ***@posting.goo gle.com...
I've got a set of emails in a table, where sometimes they've failed to
input the @ properly.
Eg james.foreman'a bcmail.co.uk
How do I write a replace to deal with that?
Also, I've got a set of names in a table, all in lower case. I can
easily capitalise the first letter of each name, but if it's somebody
like O'Brien, I'd like to get that right too (so need to be able to
identify letters after a ' )
Thanks
James
