Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186

    Unanswered: To have a column with UPPER function

    Hi,
    I have to add a column in a table which should have a UPPER function.
    To be more clear:

    Say table EMPLOYEE has a varchar column empname, now i want to add a column Upper_empname which will have all the names in column empname as UPPER CASE inserted in Upper_empname column...

    Hope this is clear to all,
    Need some suggestions urgently,

    Prashant
    Prashant

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: To have a column with UPPER function

    Originally posted by dahalkar_p
    Hi,
    I have to add a column in a table which should have a UPPER function.
    To be more clear:

    Say table EMPLOYEE has a varchar column empname, now i want to add a column Upper_empname which will have all the names in column empname as UPPER CASE inserted in Upper_empname column...

    Hope this is clear to all,
    Need some suggestions urgently,

    Prashant
    You would use a trigger to do this:

    CREATE TRIGGER t AFTER INSERT OR UPDATE OF empame ON employee
    FOR EACH ROW
    BEGIN
    :NEW.upper_empname := UPPER(:NEW.empname);
    END;
    /

  3. #3
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi Andrew,
    Thanks for your quick response...

    I have already tried it this way..

    create table OWNER.TEST(
    OFFICEID number NOT NULL,
    CONTACTID number NOT NULL,
    FIRSTNAME varchar2(64) DEFAULT ' ' NULL,
    CFIRSTNAME varchar2(40) DEFAULT ' ' NULL,
    BUSINESSID number ,
    CONTACTROLE number NULL,
    MODIFIEDON date NULL,
    PRIMARY KEY (OFFICEID,CONTACTID)
    )
    ;

    CREATE OR REPLACE TRIGGER owner.ti_test
    BEFORE INSERT ON owner.TEST
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
    SELECT UPPER(:NEW.FIRSTNAME) INTO :NEW.CFIRSTNAME
    FROM DUAL;
    END;

    Insert into OWNER.TEST (OFFICEID,CONTACTID,FIRSTNAME) values (1,1,'Prashant')

    This will give you the output as PRASHANT for column CFIRSTNAME.

    Let me know if you get a better solution.

    -Prashant
    Prashant

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by dahalkar_p
    Hi Andrew,
    Thanks for your quick response...

    I have already tried it this way..

    create table OWNER.TEST(
    OFFICEID number NOT NULL,
    CONTACTID number NOT NULL,
    FIRSTNAME varchar2(64) DEFAULT ' ' NULL,
    CFIRSTNAME varchar2(40) DEFAULT ' ' NULL,
    BUSINESSID number ,
    CONTACTROLE number NULL,
    MODIFIEDON date NULL,
    PRIMARY KEY (OFFICEID,CONTACTID)
    )
    ;

    CREATE OR REPLACE TRIGGER owner.ti_test
    BEFORE INSERT ON owner.TEST
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
    SELECT UPPER(:NEW.FIRSTNAME) INTO :NEW.CFIRSTNAME
    FROM DUAL;
    END;

    Insert into OWNER.TEST (OFFICEID,CONTACTID,FIRSTNAME) values (1,1,'Prashant')

    This will give you the output as PRASHANT for column CFIRSTNAME.

    Let me know if you get a better solution.

    -Prashant
    Your solution is more ore less the same as mine, but will be slower. You say:

    SELECT UPPER(:NEW.FIRSTNAME) INTO :NEW.CFIRSTNAME
    FROM DUAL;

    I say:

    :NEW.CFIRSTNAME := UPPER(:NEW.FIRSTNAME);

    There is no need for SELECT FROM DUAL, and it will make your trigger slower.

  5. #5
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    How about going for a function based index.

    create index TEST01 on OWNER.TEST (UPPER(CFirstname));

    Prashant
    Prashant

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    That would help if you are using in your queries WHERE clause something like WHERE UPPER(CFirstname) = '.........'

    Without function based index, with the above WHERE clause the query will not use any index on the CFirstname column. But when you create function based index on this column the index will be used, nothing else.

    I don't see how that will help you so the CFirstname column has it's values in upper case. You have to have ON INSERT trigger to transform every newly inserted value into UPPER(CFirstname) value.


    Hope that helps,

    clio_usa
    OCP - DBA

  7. #7
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi clio,

    Yes that's true, actually i forgot to mention that i would use both FBI and Trigger for performance tuning. Trigger to insert the values in CFirstname and FBI for enhancements in better response for a query.

    Thank's,
    Prashant
    Prashant

Posting Permissions

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