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.listes@codata.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@postgresql.org)
>>  To make changes to your subscription:
>>  http://www.postgresql.org/mailpref/pgsql-general
>>
>