Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2012
    Posts
    9

    Question Unanswered: CHECK constraint with SUBSTR

    How to insert only alphabets by using check constraint with substr function.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    remove all the letters & check length of remaining > 0
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Length(trim(translate(upper(column), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', ' '))) = 0
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2012
    Posts
    9

    CHECK constraint

    HI,
    I am learner of ORACLE. This is my assignment. I created table with following details BUT

    CREATE table kcb_acc_tab(
    ACNO number(4) constraint pk_acno primary key,
    NAME varchar2(30) constraint nn_name not null constraint ck_name check(name=upper(name)),
    ACTYPE char constraint ck_actype check(ACTYPE in ('S','C','R')),
    DOO timestamp default sysdate,
    BAL number(10,2),
    constraint ck_actype_bal check((ACTYPE='S' and BAL>=5000) or
    (ACTYPE='C' and BAL>=10000) or
    (ACTYPE='R' and BAL>=5000)
    )

    In this table
    column name "NAME" should accept only alphabets [A to Z]. I tried but i am not getting that result.
    plz help me.

    THANK YOU.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    NAME is Reserved Word & should NOT be used as column name

    use what beilstwh posted
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jul 2012
    Posts
    9

    CHECK constraint

    THANK YOU FOR YOUR KIND INFORMATION...NOW I CHANGED SO WHAT IS THE PROCESS TO GET ONLY ALPHABETS IN AC_HOLD_NAME

    CREATE table kcb_acc_tab(
    ACNO number(4) constraint pk_acno primary key,
    AC_HOLD_NAME varchar2(30) constraint nn_name not null constraint ck_name check(ac_hold_name=upper(ac_hold_name)),
    ACTYPE char constraint ck_actype check(ACTYPE in ('S','C','R')),
    DOO timestamp default sysdate,
    BAL number(10,2),
    constraint ck_actype_bal check((ACTYPE='S' and BAL>=5000) or
    (ACTYPE='C' and BAL>=10000) or
    (ACTYPE='R' and BAL>=5000)
    )
    );

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Length(trim(translate(upper(column), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', ' '))) = 0
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I found this description in SQL Language Reference.

    To remove all characters in from_string,
    concatenate another character to the beginning of from_string
    and specify this character as the to_string.
    For example, TRANSLATE(expr, 'x0123456789', 'x') removes all digits from expr.
    TRANSLATE

    Length(translate(upper(column), '0ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0')) = 0
    Last edited by tonkuma; 07-07-12 at 20:14.

  9. #9
    Join Date
    Jul 2012
    Posts
    9

    CHECK constraint

    Thank you sir.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •