> Does anyone know how to delete double quotes from my entries '"'.
> I thing you can use regular expressions.
Dexter,
The following resources may be of interest to you:
The MySQL Manual, particularly:
7.1 Literals: How to Write Strings and Numbers,
7.4 Functions for Use in Select and Where Clauses,
7.4.6 String Comparison Functions, and
7.4.10 String Functions
Also real inspiration in a recent posting on this list (on a similar problem) is "RE:
UPDATE problem" from Steve Meyers
to Amit.
[That and your question managed to get my 'creative juices' flowing this morning -
thanks]
The 7.1 manual entry discusses literals, so if you're uncertain about being able to
enclose double-quotes (") in a
string ...
Next ambiguity: the word "delete" might lead you to the SQL command: DELETE. In fact
you want to UPDATE your data (by
deleting/removing...)
The title of the 7.4 manual entry is a great hint as to problem solving methodology -
you might be heading for an UPDATE
command, but the WHERE clause is common to both SELECT and UPDATE and thus you can use
SELECT to build and debug that
far - rather than unnecessarily risking errors leading to data corruption and db
restores (from that ever elusive backup
copy...) While on this topic, you can also work on the SET clause by quoting 'sample
data' directly without using a
TABLE (many examples in the manual entries listed), so we CAN save databases (as well
as the whales!)
Next ambiguity: I made the casual assumption that the double quotes in the "entries"
were surrounding strings (ie first
and last character of the field). You didn't indicate if this was so, or if they are
'randomly scattered' within the
string data - Notice that the WHERE clause below handles both situations, but the SET
clause does not!!!
The 7.4.6 manual entry discusses the % wild-card character and thus how to build the
WHERE clause.
The 7.4.10 manual entry lists a whole range of functions - yes you could use a REGEX
solution, but (I'm not very good at
those and) the simplest approaches are always the best. Have settled on the TRIM
command which (I always think is only
useful to remove leading/trailing spaces, but) is a multi-purpose tool when you decide
which character(s) it should work
on. Of course you could also use substring() and length(), etc, etc.
Another little gripe: it would be easier if you gave us more information about your
data/what you have tried before
posting a request on the list. Then we don't have to 'invent' or 'guess' what your
data/table looks like!
UPDATE tbl_dexter
SET str_right = TRIM(BOTH '"' FROM str_right)
WHERE str_right LIKE '%"%';
- the TRIM string argument is single-quote then double-quote then single-quote!
If the double-quotes are (also) 'embedded' in string str_right, this won't be enough!
Please advise,
=dn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
