That's why I told you to keep on running it until it was all fixed. Every time there will be one less white space, until you're down to just one. Steve Meyers
> -----Original Message----- > From: Adrian D'Costa [mailto:[EMAIL PROTECTED]] > Sent: Friday, October 19, 2001 3:44 AM > To: Steve Meyers > Cc: Mysql Mailing List > Subject: RE: how to get the correct result -- Thrid Time -- > > > On Fri, 19 Oct 2001, Steve Meyers wrote: > > > 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 :) > > Well that would only get the first two spaces. I do not know how many > spaces are there > > Adrian > > > > > > 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 > > --------------------------------------------------------------------- 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
