Well, you still have white space in there. Try running the following until it's fixed:
UPDATE hotel SET nome_hotel=replace(nome_hotel, ' ', ' ') That will replace any double spaces to one space. If there's still a problem, replace all tabs (\t) and carriage returns (\r) with a space, and repeat the above. And from now on, make sure the data gets put in right :) Steve Meyers > -----Original Message----- > From: Adrian D'Costa [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 18, 2001 11:22 PM > To: Steve Meyers > Cc: DL Neil; Mysql Mailing List > Subject: RE: how to get the correct result -- Thrid Time -- > > > On Thu, 18 Oct 2001, Steve Meyers wrote: > > > Trim won't work because the newline is in the middle of the > string. Try using the REPLACE function, ie > > > > UPDATE hotel SET nome_hotel=replace(nome_hotel, '\n', '') > > Ok it replaced it. > > > > > For the second question, it actually doesn't matter if it's > CAPS or not. The group by will be done in a case insensitive > manner. However, if you really need it lowercase, try this: > > > > SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1 > > The result is > mysql> SELECT id, LOWER(nome_hotel) FROM hotel GROUP BY 2 limit 10; > +------+---------------------+ > | id | LOWER(nome_hotel) | > +------+---------------------+ > | 3825 | | > | 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 | > | 8284 | aeneas | > +------+---------------------+ > > Atain 5208 and 3063 are the same so is 9757 and 8917. I just want only > one of them to appear. > > Thanks > > Adrian > > > > > Steve Meyers > > > > > > > -----Original Message----- > > > From: DL Neil [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, October 18, 2001 4:46 AM > > > To: Adrian D'Costa; Mysql Mailing List > > > Subject: Re: how to get the correct result -- Thrid Time -- > > > > > > > > > You didn't appear to answer last time (grump) - reply repeated below. > > > =dn > > > > > > ----- Original Message ----- > > > From: "Adrian D'Costa" <[EMAIL PROTECTED]> > > > To: "Mysql Mailing List" <[EMAIL PROTECTED]> > > > Sent: 18 October 2001 05:34 > > > 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 > > > > > > > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > > > 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 > > > > > > > > > > > > I have some records that I need to group by a field. This > is easy using > > > ... > > > > 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. > > > ... > > > > 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? > > > > > > Adrian, > > > Can you do these (both) at the time the data is first entered > into the db? > > > In other words adjust your business rules at the front end, > > > instead of attempting something that's > > > very difficult at the back? > > > =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 > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > > 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 > > > > > > > > > > > > --------------------------------------------------------------------- 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
