oracle compound Check constraint

Say I want to allow in a table column only strings that begin w开发者_C百科ith a numeral and should not contain '$' in it. How would a check constraint on this table look like?

Assuming Oracle 10g+, you can use Oracle's regex functionality in a CHECK constraint:

ALTER TABLE YOUR_TABLE
ADD CONSTRAINT col_regx CHECK REGEXP_LIKE(column_name,'^[[:digit:]]{1}[^$]*$')); 

References:

• http://www.dba-oracle.com/t_regular_expressions_constraints_updates_columns.htm
• http://psoug.org/reference/regexp.html
• http://www.techonthenet.com/oracle/check.php

Sounds like you need to use Oracle's Regular Expressions. Useful link for syntax requirement for the corresponding IF constraint can be found below

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

The regex would look like ^\d[^$]*$

I don't know Oracle so forgive me if this doesn't actually help you.

I would do something like

ALTER TABLE YOUR_TABLE ADD CONSTRAINT col_regx CHECK (substr(column_name,1,1) between '0' and '9' and column_name not like '%$%')

(not tested but probably faster than a regex)