Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010

    Unanswered: creating column by coming columns.

    Hi Guys, I had been working in mssql and i am new to oracle.
    i am creating a table of three columns

    table student_details:-

    firstname | lastname | fullname

    ramesh | kumar | ramesh kumar

    what i want is to create a table and an automated column. for example, after we insert values in firstname and lastname columns, it wll combine data from both the columns and put that into the third column. firstname - rajesh. lastname - kumar. fullname - rajesh kumar.

    Check out the syntax i use for creating this kind of table in mssql.

    create table student_details (firstname char(20), lastname char(20), fullname as (firstname + lastname) persisted;

    after teh table creation, when we enter data for firstname and lastname, the third column gets filled automatically.

    can i have syntax for creating same kind of table in Oracle?

    Thanks a lot, guys

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    First: in SQL, string concatenation is done using the || operator.
    So you would need to use firstname || lastname, not firstname + lastname
    But most probably you want: firstname ||' '|| lastname to get a space between the two values.

    Secondly: first rule of a relational database: do not store values that can be calculated by the existing data - especially in this case.

    If you need the full name in a query, just query for it:
    SELECT firstname ||' '|| lastname as fullname 
    FROM student_details
    If you don't want to write that every time, create a view that returns the information.

    Third: You don't want char(20), you want varchar(20).

    Fourth: the syntax for a virtual column is documented in the manual:
    Please consult the manual first before asking syntax questions.

    And finally: please use [code] tags when you post SQL code or other text where you want fixed-width display.
    For details see the help:

Posting Permissions

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