Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: What is the meaning of || in insert values???

    i saw a query like
    INSERT INTO TEST VALUES(a || b || NULL || C || NULL || D);

    what is the meaning of this query???

    Jake

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    excellent question

    double pipes concatenate, but NULL concatenated to anything is NULL

    at least, in standard sql

    to be honest, not being an oracle dba, i couldn't swear it works the same way in oracle

    i don't have an oracle system to test on, and i'm too lazy to look it up in their online docs

    i know, i know, too many excuses...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    107
    i have tested using the below procedure... concatenate with NULL has no effect on oracle....

    ----------------------------------------------------------------------------------
    create table CONCATWITHNULL(test VARCHAR2(1000))
    /
    CREATE OR REPLACE PROCEDURE ConcatWithNullProc
    AS
    test VARCHAR2(20) := 'test';
    test1 VARCHAR2(20) := 'test1';
    test2 VARCHAR2(20) := 'test2';
    BEGIN
    INSERT INTO CONCATWITHNULL VALUES(test || test1 || test2);
    INSERT INTO CONCATWITHNULL VALUES(test || NULL || test1 || NULL || test2);
    END;
    /
    ----------------------------------------------------------------------------------

    result of execution...
    ----------------------------------------------------------------------------------
    SQL> select * from ConcatWithNull
    2 /

    TEST
    --------------------------------------------------------------------------------
    testtest1test2
    testtest1test2

    ----------------------------------------------------------------------------------

    Jake

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Null are meaningful in an insert if you have columns that you want to store a null value in.

    insert into my_table values(a,null,b,c);

    However the way your insert statement is written, makes no sense.

    INSERT INTO TEST VALUES(a || b || NULL || C || NULL || D);
    should be
    INSERT INTO TEST VALUES(a || b || C || D);
    and the table test contains only 1 column
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by r937
    double pipes concatenate, but NULL concatenated to anything is NULL

    at least, in standard sql

    to be honest, not being an oracle dba, i couldn't swear it works the same way in oracle
    Someone else has already verified that concatenating NULL to a string does not result in a NULL in Oracle - i.e. Oracle is non-standard w.r.t. NULL strings. The reason is that Oracle unfortunately fails to distinguish between a NULL and an empty string (''). Oracle is therefore forced to behave in a non-standard manner one way or the other, and x||null = x has been preferred over x||'' = null!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, tony, thanks for the corroboration

    remind me to be very careful in future answering sql questions in an oracle environment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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