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

From: John Hurley <johnbhurley_at_sbcglobal.net>
Date: 27 Nov 2004 16:00:10 -0800
Message-ID: <d4d6f278.0411271600.263b1ad2@posting.google.com>

> 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.

You can do this with either a check constraint on the field/column definition or via a trigger. The oracle documentation references for check constraint or trigger are one place to check, or the ask tom site within oracle.

It is possible to store all 11 characters including the hyphens but many people would recommend only storing the 9 numeric character and have the hyphens inserted/removed as the data is presented/stored.

If you really prevent the insert/update of a row that has "invalid" data you have to make sure the application handles these rejections gracefully. Received on Sat Nov 27 2004 - 18:00:10 CST