Thanks DL,
Seems there was double quotes at the begining of each field due
to importing data into a table using the "Load infile" statement with the
import file being DOS format rather than Unix.
As all know unix lines end with a '\n' only. When I did a test in a
row and deleted the '"' , it worked fine.
Dexter
>From: "DL Neil" <[EMAIL PROTECTED]>
>Reply-To: "DL Neil" <[EMAIL PROTECTED]>
>To: "Dexter Coelho" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
>Subject: Re: delete doble quotes from entries?
>Date: Sun, 21 Oct 2001 10:09:31 +0100
>
> > 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
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
---------------------------------------------------------------------
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
