Column definition/restriction

[eyetry]

is there a way to add a mask or restriction to an alphanumeric column in a way that it will not allow spaces?

ie... 'value1 value2' woould not be valid but any of the following would? value1value2', 'value1-value2', 'value1_value2'

If not is it possible to define a column that way?

 

[Andrzejek]

I don't know if you can define the column this way, but you can set a Triger to get rid of all speces when the field is Inserted/Updated.

Have fun.

---- Andy

 

[SantaMufasa]

Yes...we can enforce such a limitation with a CHECK constraint, similar to the following:

SQL> create table eyetry (x varchar2(20)
          constraint eyetry_disallow_blanks
              check (length(replace(x,' ',null)) = length(x)));

Table created.

SQL> insert into eyetry values ('123456');

1 row created.

SQL> insert into eyetry values ('1234-5678.90123_');

1 row created.

SQL> insert into eyetry values ('1234 5678');
insert into eyetry values ('1234 5678')
*
ERROR at line 1:
ORA-02290: check constraint (DHUNT.EYETRY_DISALLOW_BLANKS) violated

Let us know if this suits your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

eyetry,

alternatively (and I believe slightly simpler) constraint the column to be

CHECK INSTR(<COLUMN)_NAME>,' ') = 0;

This ought to be more efficient that Santa's method. Anyway, he already knows far too much about Oracle for his own good

Regards

T

 

[SantaMufasa]

HeHeHe, Tharg...Good catch. My method reminds me of the Obstetrician/Gynecologist who became tired of his profession and decided to retrain as an auto mechanic.

The final exam for his auto-mechanics course was to rebuild and re-install a carburetor into an auto. The former doctor was thrilled to receive an "A+" on his work. He asked the instructor why he had received such a high grade. The instructor replied: "Full credit for the rebuild...full credit for the re-installation...and Extra credit for doing it all through the tail pipe."

I was just solving Eyetry's request, "through the tail pipe." <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”