Thread: Like with special character

Like with special character

From
tnodev
Date:
Hello,

I'm using postGre with tables which contain French character (éèçàù...). 
Is there a fonction which performs a like in replacing é (e cute) by e ?

thanks...


Re: Like with special character

From
Guillaume LELARGE
Date:
Hi,

tnodev a écrit :
> I'm using postGre with tables which contain French character (éèçàù...).
> Is there a fonction which performs a like in replacing é (e cute) by e ?
> 

select translate('forêt', 'àâäéèêëîïôöùûü', 'aaaeeeeiioouuu');

Change the first word (forêt) by the string you want characters to be
replaced.

For more, see : http://docs.postgresqlfr.org/pgsql-8.1.3-fr/functions-string.html   (french docs)
http://www.postgresql.org/docs/8.1/interactive/functions-string.html  (english docs)
 

BTW, it is PostgreSQL, not postGre.

Regards,


-- 
Guillaume.


Re: Like with special character

From
Oleg Bartunov
Date:
On Mon, 1 May 2006, tnodev wrote:

> Hello,
>
> I'm using postGre with tables which contain French character (?????...). Is 
> there a fonction which performs a like in replacing ? (e cute) by e ?

to_ascii() should helps you

>
> thanks...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83


Re: Like with special character

From
TNO
Date:
Perfect, thanks

I found regex for postgreSql too, it's interesting too

select * from divers
where s_libelle ~* '^t(e|é|ê)'



Oleg Bartunov a écrit :
> On Mon, 1 May 2006, tnodev wrote:
>
>> Hello,
>>
>> I'm using postGre with tables which contain French character
>> (?????...). Is there a fonction which performs a like in replacing ?
>> (e cute) by e ?
>
> to_ascii() should helps you
>
>>
>> thanks...
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
>     Regards,
>         Oleg
> _____________________________________________________________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
>

-- 
Il n'y a pas de mauvais langage, il n'y a que des bons programmeurs




Re: Like with special character

From
Emi Lu
Date:
>
>
>> I'm using postGre with tables which contain French character 
>> (?????...). Is there a fonction which performs a like in replacing ? 
>> (e cute) by e ?
>
> to_ascii() should helps you

I got the following error:

select to_ascii('ê');
ERROR:  encoding conversion from UNICODE to ASCII not supported

Some comments about it.




Re: Like with special character

From
Osvaldo Kussama
Date:
<br /><b><i>Emi Lu <emilu@encs.concordia.ca></i></b> escreveu:<blockquote class="replbq" style="border-left: 2px
solidrgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br />><br />><br />>> I'm using postGre with
tableswhich contain French character <br />>> (?????...). Is there a fonction which performs a like in replacing
?<br />>> (e cute) by e ?<br />><br />> to_ascii() should helps you<br /><br />I got the following
error:<br/><br />select to_ascii('ê');<br />ERROR: encoding conversion from UNICODE to ASCII not supported<br /><br
/>Somecomments about it.<br /><br /></blockquote><br /><br />From the docs:<br
/>http://www.postgresql.org/docs/8.1/interactive/functions-string.html#FTN.AEN7612<br/><br />Notes on table 9-6. Other
StringFunctions:<br />"The to_ascii function supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings
only."<br /><br />Try convert encoding:<br /><br />bdteste=# \set<br />VERSION = 'PostgreSQL 8.1.4 on
i386-redhat-linux-gnu,compiled by GCC i386-redhat-linux-gcc (GCC) 4.1.0 20060304 (Red Hat 4.1.0-3)'<br />....<br
/>ENCODING= 'UTF8'<br />......<br /><br />bdteste=# select to_ascii('ê');<br />ERRO:  conversão de condificação de UTF8
paraASCII não é suportada<br /><br />bdteste=# select to_ascii('ê','LATIN1');<br /> to_ascii<br />----------<br
/> Aa<br/>(1 registro)<br /><br />bdteste=# SELECT convert('ê', 'UTF8', 'LATIN1');<br /> convert<br />---------<br
/><br/>(1 registro)<br /><br />bdteste=# SELECT to_ascii(convert('ê', 'UTF8', 'LATIN1'));<br />ERRO:  conversão de
condificaçãode UTF8 para ASCII não é suportada<br /><br />bdteste=# SELECT to_ascii(convert('ê', 'UTF8', 'LATIN1'),
'LATIN1');<br/> to_ascii<br />----------<br /> e<br />(1 registro)<br /><br />[]s<br />Osvaldo<br /><br /><p><hr
size="1"/> O Yahoo! está de cara nova. Venha <a
href="http://us.rd.yahoo.com/mail/br/tagline/spirit/*http://br.yahoo.com/preview">conferir</a>!