SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
Thread: mysql command
-
Apr 17, 2002, 23:24 #1
- Join Date
- Jan 2001
- Location
- Chapel Hill, NC
- Posts
- 160
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
mysql command
is there a mysql command that will change a certain number of characters to something else?
the problem is someone scripted a lyrics script, except it takes each quote and makes it multiple quotes. i don't even know ow to fix it, but i want to at least update the database so it doesn't do '' instead of '
how would i go about changing all the '' to ' ?
thanks!
-
Apr 22, 2002, 02:04 #2
- Join Date
- Feb 2002
- Location
- UK
- Posts
- 146
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
i assume this was done to counter the problem of inserting data that contains single quotes into a database.
see http://www.4guysfromrolla.com/webtech/051899-1.shtml for an example using asp.
one option would be to just convert double quotes back to single quotes after retrieving the info from the db.
Alternativley this might be useful for you.
the string replace function:
http://www.mysql.com/doc/S/t/String_functions.html
you should be able to use it like this:
UPDATE table SET field = REPLACE(field, '\"', '\'');
this will replace *all* instances of " with ' for that field.
best check it on a test database first though
-
Apr 23, 2002, 18:17 #3
- Join Date
- Jan 2001
- Location
- Chapel Hill, NC
- Posts
- 160
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
there seemed to be an error with that... i couldn't get it to work...
are the three apostrophes at the end supposed to be there?
-
Apr 24, 2002, 03:14 #4
- Join Date
- Feb 2002
- Location
- UK
- Posts
- 146
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
sorry, that should have been.
UPDATE table SET field = REPLACE(field, '\"', '\'');
or in case this post messes up again
UPDATE table SET field = REPLACE(field, '\(double quotes)', '\(single quotes)');
you would replace (double quotes) with " and (single quotes) with '
those darn escaping chars again
hope this is clear and hope it works this time
-
Apr 26, 2002, 00:32 #5
- Join Date
- Jan 2001
- Location
- Chapel Hill, NC
- Posts
- 160
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
argh, when i tried to do
update lyrics set lyrics = replace(lyrics,'\(double apostrophes)','\(single quote)');
it gave me an error saying that \(double quotes)' is not a known command... SO i tried changing all the single quotes to semicolons then tried changing two semicolons (since '' would become ;back into single quotes but now they've simply dissappeard from my database all together
-
Apr 26, 2002, 00:45 #6
- Join Date
- Mar 2001
- Location
- Mexico
- Posts
- 1,287
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by honging
argh, when i tried to do
update lyrics set lyrics = replace(lyrics,'\(double apostrophes)','\(single quote)');
it gave me an error saying that \(double quotes)' is not a known command... SO i tried changing all the single quotes to semicolons then tried changing two semicolons (since '' would become ;back into single quotes but now they've simply dissappeard from my database all together
the first one : single quote + double quote + single quote,
the second one : double quote + single quote + double quote.
That way you don't need to escape the quotes.
Now in php that should be written as
$sql = 'update lyrics set lyrics = replace(lyrics,\\'"\\',"\\'")';
Paul
-
Apr 26, 2002, 03:14 #7
- Join Date
- Feb 2002
- Location
- UK
- Posts
- 146
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
sorry, i obviously wasn't clear enough in my second post and confused the matter
i only used the (double quotes) (single quotes) stuff as one of the \ chars disappeared from my first post.
looks like Paul has sorted it out tho' with his clearer answer
Bookmarks