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
