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', '') 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 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
