Re: Prevent characters not transposable to LATIN9

Le 30/06/2010 2:42, Howard Rogers a écrit :
> Something I do in Oracle: do a TRANSLATE on whatever string is being
> supplied, converting matching characters to spaces, and measure the length.
> If the length is greater than zero, your supplied string has something in it
> you're not expecting, at which point you can intervene and warn your users
> with some sort of error message. For example:

Hi Howard, sorry for the late reply.
Yes, that could work indeed.
I did not want to take the hassle of having to list all acceptable
characters, but that might be the only way.

Thanks for the hint !

>
> ims=# select length(translate('MYSTRING','ABCDEFGHIJKLM',' '));
> ?column?
> ----------
> 5
> (1 row)
>
>
> It's '5' because I have no translation specified for 'Y', 'S' or 'T', 'R' or
> 'N'. If I add those in, then I get zero:
>
> ims=# select length(translate('MYSTRING','ABCDEFGHIJKLMYSTRN',' '));
> ?column?
> ----------
> 0
> (1 row)
>
>
> So, in that second argument to the translate function, specify all the
> characters you're prepared to accept, and test form input for the length
> being zero after the translate function has applied.
>
> Regards
> HJR
>
>
> On Wed, Jun 30, 2010 at 12:52 AM, Arnaud Lesauvage
> <arnaud(dot)listes(at)codata(dot)eu>wrote:
>
>> Hi list !
>>
>> We have a database in UTF8, from which we have to export text files in
>> LATIN9 encoding (or WIN1252, which is almostthe same I believe).
>>
>> Records are entered via MSAccess forms (on psqlodbc-linked tables).
>> The problem is that some of the characters input by the users have no
>> equivalent in LATIN9.
>>
>> How could I easily write a CONSTRAINT (or RULE) that would check that
>> everything entered in the fields have an equivalent in my specific
>> destination encoding ?
>>
>> Thanks for any hints on this !
>>
>> Regards
>> --
>> Arnaud Lesauvage
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>