-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 OK it looks like you have gotten rid of your excess carriage returns, but now you need to remove the excess spaces. did you try nesting the functions?
try this select replace(replace(nome_hotel,\n,""), " ", " ") as nome d'hotel from hotel group by nome_hotel limit 10; you can update your entire database and remove the excess spaces and returns by doing the following update hotel set nome_hotel to replace(replace(nome_hotel,\n,""), " ", " ") HTH On Fri, 19 Oct 2001, Adrian D'Costa wrote: | Date: Fri, 19 Oct 2001 10:43:31 +0530 (IST) | From: Adrian D'Costa <[EMAIL PROTECTED]> | To: Clyde Jones <[EMAIL PROTECTED]> | Subject: Re: how to get the correct result -- Thrid Time -- | | Hi, | | Thanks. It gave an error but it work after adding the "\n". Now the | result is: | | +------+-----------------------------+ | | id | replace(nome_hotel,"\n","") | | +------+-----------------------------+ | | 3825 | | | | 3827 | | | | 3391 | Abi d'Oru | | | 5208 | Abou Nawas Djerba | | | 3063 | Abou Nawas Djerba | | | 1252 | Adams Beach | | | 9757 | Aegean Village | | | 7973 | Aegean | | | 8917 | Aegean Village | | | 8122 | Aegeon | | +------+-----------------------------+ | | If you notice id 5208 and 3063 are the same so also with 9757 and 7973. I | tried using trim it is the same. Any pointers. | | Thanks | | Adrian | | On Thu, 18 Oct 2001, Clyde Jones wrote: | | > the doc page is here | > http://www.mysql.com/doc/S/t/String_functions.html | > | > try | > | > select replace(nome_hotel,\n,"") | > from hotel | > group by nome_hotel limit 10; | > | > On Thu, 18 Oct 2001, Adrian D'Costa wrote: | > | > | Date: Thu, 18 Oct 2001 10:04:03 +0530 (IST) | > | From: Adrian D'Costa <[EMAIL PROTECTED]> | > | To: Mysql Mailing List <[EMAIL PROTECTED]> | > | Subject: how to get the correct result -- Thrid Time -- | > | | > | Hi, | > | | > | I have some records that I need to group by a field. This is easy using | > | group by in the sql statement. What I happening is that I get some data | > | in html format that I have written a script that will extract the data I | > | require an dump it into a table. Everything working fine. The problem is | > | that when the data in entered into the table some fields enter with the | > | new line (\n). So when I use the group by command below is the result. | > | | > | mysql> select nome_hotel from hotel group by nome_hotel limit 10; | > | | Abou | > | Nawas Djerba | | > | | Abou Nawas Djerba | | > | | Adams Beach | | > | | Aegean | > | Village | | > | | Aegean | | > | | Aegean Village | | > | | > | This what I don't want since "Abou | > | Nawas Djerba" and "Abou Nawas Djerba" are the | > | same. How do I get rid of the space. I tried trim, rtrim nothing works. | > | | > | Second, using the same data I get some of the hotel names in CAPS and I | > | need to convert it to lower and then group by. How do I get these two | > | done? | > | | > | Thanks | > | | > | Adrian | > | | > | > | - -- "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." - Benjamin Franklin Clyde Jones - http://www.clydec.net -----BEGIN PGP SIGNATURE----- Version: PGP 6.5.8 iQA/AwUBO9ArzkP80ESqUED/EQJ3FwCgoFMh4HgKtwNlgwNK0IflH5VHkkAAn0HP pO6A3kYZgTTUN/uFgz54J57A =TgdS -----END PGP SIGNATURE----- --------------------------------------------------------------------- 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
