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
