Professional Web Applications Themes

Combining Fields - Microsoft SQL / MS SQL Server

Look up 'ISNULL' and COALESCE' in BOL. "Greg" <gmanshipwvmls.com> a écrit dans le message de news: [email]us3E67MQDHA.3192tk2msftngp13.phx.gbl[/email]... > Hello, > > I have setup a view with a column that combines address fields as seen > below: > > Column > AddNum + N' ' + AddDir + N' ' + AddStreet > > The obvious problem is that if there is no data in the AddDir field, it > returns the address as <NULL>. What is the best way of returning all the > addresses regardless if data is missing in any of the three fields. Any > help ...

  1. #1

    Default Re: Combining Fields

    Look up 'ISNULL' and COALESCE' in BOL.

    "Greg" <gmanshipwvmls.com> a écrit dans le message de news: [email]us3E67MQDHA.3192tk2msftngp13.phx.gbl[/email]...
    > Hello,
    >
    > I have setup a view with a column that combines address fields as seen
    > below:
    >
    > Column
    > AddNum + N' ' + AddDir + N' ' + AddStreet
    >
    > The obvious problem is that if there is no data in the AddDir field, it
    > returns the address as <NULL>. What is the best way of returning all the
    > addresses regardless if data is missing in any of the three fields. Any
    > help would be appreciated. Thanks in advance.
    >
    > Greg
    >
    >
    Guest

  2. #2

    Default Combining Fields

    I'm very new to SQL and i'm having a problem combining
    multiple fields to create one ID Key field. I know you
    can't combine different data types, but the problem seems
    to be if any field in the string I'm attempting to create
    is NULL then the whole string returns NULL. How can I
    exclude NULL fields from the string?
    Dustin Guest

  3. #3

    Default Re: Combining Fields

    Wrap all those fields with isNull function. Look for isNull in BOL.

    HTH
    Srinivas Sampangi

    "Dustin" <net> wrote in message
    news:3d4f01c355da$91021490$gbl... 


    sampangi Guest

  4. #4

    Default Re: Combining Fields

    Can you explain more in depth? 
    isNull in BOL. [/ref]
    seems [/ref]
    create 
    >
    >
    >.
    >[/ref]
    Dustin Guest

  5. #5

    Default Re: Combining Fields

    here is one simple example

    Declare str1 varchar(20)
    , str2 varchar(20)


    Select str1 = 'Value in str1'

    select str1 + str2 -- This returns NULL as str2 is NULL


    OUTPUT


    --------------------
    NULL

    (1 row(s) affected)


    select isNull(str1,'') + isNull(str2,'') -- wrapping each field with
    isNull

    OUTPUT

    ----------------------------------------
    Value in str1

    (1 row(s) affected)


    If you can post the table definitions , sample data and expected output we
    can help you better.


    HTH
    Srinivas Sampangi


    "Dustin" <net> wrote in message
    news:008401c355de$e23412b0$gbl... 
    > isNull in BOL. [/ref]
    > seems [/ref]
    > create 
    > >
    > >
    > >.
    > >[/ref][/ref]


    sampangi Guest

Similar Threads

  1. Combining 2 Fields Using ListBox.DataTextField
    By Nathan Sokalski in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: November 10th, 08:49 AM
  2. combining 2 datasource fields into hyperlink column
    By Ben in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: July 26th, 04:11 PM
  3. Replies: 0
    Last Post: November 21st, 04:58 PM
  4. #26351 [NEW]: Incorrect handling of Null Fields/Numerical Fields with '0'
    By jabberwocky at ibplanet dot com in forum PHP Development
    Replies: 0
    Last Post: November 21st, 04:47 PM
  5. newbie question: combining fields
    By John in forum PHP Development
    Replies: 1
    Last Post: August 31st, 10:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139