Re: How to enforce a string format in a field??

On 27 Nov 2004 02:45:51 -0800, ramon_at_conexus.net (Ramon F Herrera) wrote:

>My database contains a field called 'SSN' whose
>contents should be obvious.
>
>Is there any way to constrain that field to the
>following format: ddd-dd-dddd? That is: if there is
>an attempt to insert a value that doesn't have 9 digits
>with the hyphens in the proper places, the record should
>be rejected.
>
>Or is this something that I should check before inserting
>the data into the table (for instance, making sure that
>the *.dat file only contains proper SSN values)??
>
>TIA,
>
>-Ramon F Herrera

Hi Ramon,

A check constraint on the column in question seems to be the most approproate solution in this case :

create table foo (c1 varchar(11));

ALTER TABLE foo ADD CONSTRAINT
c1_check CHECK (c1 like '___-__-____' and

                 ltrim(translate(c1,'-0123456789',' ')) is null and
                 length(translate(c1,'z-','z')) = 9); 

• Kenneth Koenraadt