Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Posts
    140

    Unanswered: inserting only alphabets

    hi all

    given a following table definition, is there any way to restrict numbers
    being inserted, i mean the insert should not allow any numbers

    create table temp (name varchar2(14));

    anything like rules or check constraint or something else ?

  2. #2
    Join Date
    Mar 2004
    Posts
    9

    Re: inserting only alphabets

    use trigger

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    you can create a trigger on update/insert which will validate the values inserted.
    Oracle can do wonders !

  4. #4
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    if you just want to strip numbers out then in the trigger just have:

    :new.name := translate(:new.name,'a1234567890','a');

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    ALTER TABLE TEMP
    ADD CONSTRAINT CH_TEMP_NAME
    CHECK (NAME = translate( NAME, 'A1234567890', 'A'));

  6. #6
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi,

    cud u explain about the TRANSLATE FUNCTIONALITY!!!

    Thanx in advance

  7. #7
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    translate will replace characters in the first string with characters in the second. anything not matching in the second will be replaced with null .... not sure if that makes sense, try this

    select translate('aaabbb','ab','qw') from dual

    will return qqqwww

    select translate('aaabbbccc','ab','q') from dual

    will return qqqccc as a will be replaced with q and there is no corresponding replacement for b so that will be replaced with null and c is not replaced as there is no c in the second string

    select translate('a1b2c3v4b5n6m7','a1234567890','a') from dual

    will replace a with a and all numbers with null and every other character will stay the same, so the return is abcvbnm

    You have to replace at least one character, hence a is translated to a

    hope that makes a little sense, if not run the queries and it should be clearer

    Robert

  8. #8
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hi, Saravanan.R,

    Ok, about TRANSLATE-Functionality:


    ===================================
    TRANSLATE(STRING, IF-STRING, ELSE-STRING);
    ===================================

    TRANSLATE checks each char of STRING, if this char exists in this String.

    If YES (this char does exist in the String) ,
    TRANSLATE memorizes the position of this char in the String
    and gets the char on the same position in the THEN-String in exchange for the starting char of the STRING.

    E.g.:

    select TRANSLATE('Hello World!', 'eo!', '+*#') from DUAL;

    >> H+ll* W*rld#


    I hope, there are not so many mistakes here!

    Regards,
    Julia
    Last edited by julla27; 04-02-04 at 04:40.
    Regards,
    Julia

  9. #9
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi All,

    the Previous Solution accept the NUMBER also !!!

    i want to allow only ALPHABETS!!!

    Thanx in advance

  10. #10
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    PHP Code:
    SQLdesc test;
     
    Name                                      Null?    Type
     
    ----------------------------------------- -------- ----------------------------
     
    COL1                                               VARCHAR2(10)

    SQLselect from test;

    no rows selected

    SQL
    create or replace trigger t_Ins_test
      2  before insert on test
      3  
    for each row
      4  
      5  begin
      6   
    if instr(:new.col1'0') > 0 then
      7    raise_application_error
    (-20001,'Number found');
      
    8   end if;
      
    9  end;
     
    10  /

    Trigger created.

    SQLinsert into test values ('aa');

    1 row created.

    SQLinsert into test values ('aa0');
    insert into test values ('aa0')
                *
    ERROR at line 1:
    [
    b]ORA-20001Number found[/b]
    ORA-06512at "SS.T_INS_TEST"line 4
    ORA
    -04088error during execution of trigger 'SS.T_INS_TEST'


    SQLselect from test;

    COL1
    ----------
    aa

    SQL

    Oracle can do wonders !

Posting Permissions

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