* [EMAIL PROTECTED] > Thanks, that worked for song named "Empire", however, > when I checked other song names such as songname > called "One" I get two results: > > | id | songname | > +----+------------------------+ > | 35 | > You're Still The One | > | 57 | > One | > +----+------------------------+ > 2 rows in set (0.00 sec) > > How do I get exact match only between two strings or > remove whitespace before and after string?
To remove the unwanted \n or \r\n from your table, you can use the REPLACE() function: UPDATE songtable SET songname = TRIM(REPLACE(REPLACE(songname,'\n',''),'\r','')) Beware that this will remove _all_ return and linefeed characters, not only leading and trailing. The outer TRIM() function removes only leading/trailing _space_ characters. The outer TRIM() may be unnecessary if you don't have "\n title" in your data, only "\ntitle", "\rtitle" or "\r\ntitle". Removing '\r' is only needed if the data contains '\r', which it may do if the data comes from a windows environment. If the data is originally produced on your linux machine, you may simplify to: UPDATE songtable SET songname = REPLACE(songname,'\n','') > I can't seem > to find any good emaples of string comparisons for exact > match. Exact matching is done using = (the equal sign), but you don't have an exact match in this case... <URL: http://www.mysql.com/doc/S/t/String_functions.html > <URL: http://www.mysql.com/doc/S/t/String_comparison_functions.html > > Songname field is a VARCHAR(55). I thought VARCHAR saves > only the length of the string where as CHAR adds > whitespace to parts not used in the field CHAR fields are padded with _space_ characters, not any whitespace... and these space characters are automatically removed when you retrieve the data. Your \n and/or \r characters are considered a part of your string, and is not ignored. -- Roger --------------------------------------------------------------------- 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