On 12/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Nathan Gross <[EMAIL PROTECTED]> wrote on 12/20/2005 05:34:58 PM:
>
> > Hi;
> > [Mysql 4x] In a table where I get data from another program, I have
> > many records (about 1000) that have illegal chars in a [unique]
> > indexed varchar field. I would like to clean the illegal characters
> > out and leave the rest of the data intact.
> > 1. Is there a utility to do this?
> > 1b) Or an UPDATE command?
> >
> > Problem is that I do not even know which characters are illegal.
> > For the future, I can have my Java program check before the inserts.
> >
> > 2. What are the illegal characters?
> >
> > Thank you.
> > -nat
> >
>
> If you don't know what is illegal, how do you know you have illegal
> characters? Are you getting some kind of error message or warning? You have
> to give us the same information you have in order for us to make an informed
> decision.
I get two lists from different sources which I merge into the database
via a Java program. Since these two lists themselves sometimes get
their data from the same source, my program first does a SELECT on the
varchar field (unique index) [to ensure that this data is not yet in
the db] before inserting the data. (Actually I am using j2ee
ejbFinds.)
Occasionaly, say 5% of the time, I get a duplicate (create) exception
from the db, even though the db just told my program that the data was
not there!
With my debugger I verified that for these fields, if you try to
find/select this data, mysql will return a null resultset, but yet if
you try to enter this data it will throw the create exception.
Now these [defective] fields always have (1 or more) suspicious
characters like {,[,',", or commas colons, what not. I do not know
which of these is/are the culprits.
So, my little problem is twofold.
1) How to trap this data in the first place in my Java program.
Obviously I need to know what to look for!
2) I do not want to leave my database in this state, so I want to clean it.
2b)I don't mind adding a boolean field to the table to be used as a
flag for all records that have suspect data, before deciding exactly
how to clean it. Although I think I can just replace these characters
with a space, in [almost] all cases.
One more important point. This field, although unique, is NOT the pk.
I can use the PK to correctly select and display the contents of all
fields in the record.
Thank you much.
-nat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
