Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    16

    Unanswered: NULL & Concatenation

    I am using the following sql statement to concatenate fields from a sql server in my query.

    SELECT RTRIM(title) + ' ' + RTRIM(fname) + ' ' + RTRIM(lname) AS name, id FROM contact

    2 questions:

    1. How can I avoid a Null name field resulting from either fname or lname being Null? One Null field in the contatenation yields a Null field, even though the other field is not Null.

    2. Does concatentation in the sql statement reduce performance significanlty?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT RTRIM(ISNULL(title + ' ', '')) + RTRIM(ISNULL(fname + ' ', '')) + ....
    Notice how the code encloses title + '' in ISNULL - this stops you getting a ' ' at the beginning in the event of a NULL title. Ditto for fname.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh - and no
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2005
    Posts
    16
    Great, thanks.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Chief7
    Does concatentation in the sql statement reduce performance
    Actually - I feel the pedant in me stir. Concatenation in the Select statement costs nothing*. However - were you to compare a concatenated value with something else then yes - it would. That is the more precise answer to your question

    *virtually
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Smile

    Quote Originally Posted by pootle flump
    Code:
    SELECT RTRIM(ISNULL(title + ' ', '')) + RTRIM(ISNULL(fname + ' ', '')) + ....
    Notice how the code encloses title + '' in ISNULL - this stops you getting a ' ' at the beginning in the event of a NULL title. Ditto for fname.

    HTH
    Above query will remove the space between LastName and FirstName.
    Corrected query is,
    Code:
    select isnull(rtrim(LastName)+' ','')+
    isnull(rtrim(FirstName)+' ','')+....
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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