+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Help: Remove Null from SQL Results

  1. Help: Remove Null from SQL Results

    Hello,

    I have a query that is returning a null value in SQL 2008 and I cannot
    seem to get rid of it. The query displays all 78000 rows, but I only
    need it to display about 2000 rows. Basically, I am looking for all
    last names that are greater than 9 characters and using the left
    function to only return the first 9 characters. So, XYZABCKJL123
    should return XYZABCKJL in a new column I created called New_Name.
    Thank you for anyone who can help with this, I spent over 3 hours
    searching online and nothing has seemed to work.


    Select First_Name, Last_Name, 'New_Name' = CASE
    WHEN LEN(Last_Name) > 9 Then Left(Last_Name, 9)
    END
    From Table1
    Where? (what can I put in the where clause to remove all rows
    with NULL in the 'New_Name' field)



    Results Sample:

    First_Name Last_Name 'New_Name'
    Joe Smith Null
    Bobbie Miller Null
    Carl Jeffersonn Jefferson

  2. Re: Help: Remove Null from SQL Results

    joe_G expressed precisely :
    > Hello,
    >
    > I have a query that is returning a null value in SQL 2008 and I cannot
    > seem to get rid of it. The query displays all 78000 rows, but I only
    > need it to display about 2000 rows. Basically, I am looking for all
    > last names that are greater than 9 characters and using the left
    > function to only return the first 9 characters. So, XYZABCKJL123
    > should return XYZABCKJL in a new column I created called New_Name.
    > Thank you for anyone who can help with this, I spent over 3 hours
    > searching online and nothing has seemed to work.
    >
    >
    > Select First_Name, Last_Name, 'New_Name' = CASE
    > WHEN LEN(Last_Name) > 9 Then Left(Last_Name, 9)
    > END
    > From Table1
    > Where? (what can I put in the where clause to remove all rows
    > with NULL in the 'New_Name' field)
    >
    >
    >
    > Results Sample:
    >
    > First_Name Last_Name 'New_Name'
    > Joe Smith Null
    > Bobbie Miller Null
    > Carl Jeffersonn Jefferson



    WHERE LEN(IsNull(Last_name, '')) > 9

    --
    Michael Cole



  3. Re: Help: Remove Null from SQL Results

    On Tue, 10 May 2011 17:44:29 -0700 (PDT), joe_G
    wrote:

    >I have a query that is returning a null value in SQL 2008 and I cannot
    >seem to get rid of it. The query displays all 78000 rows, but I only
    >need it to display about 2000 rows. Basically, I am looking for all
    >last names that are greater than 9 characters and using the left
    >function to only return the first 9 characters. So, XYZABCKJL123
    >should return XYZABCKJL in a new column I created called New_Name.
    >Thank you for anyone who can help with this, I spent over 3 hours
    >searching online and nothing has seemed to work.
    >
    >
    >Select First_Name, Last_Name, 'New_Name' = CASE
    > WHEN LEN(Last_Name) > 9 Then Left(Last_Name, 9)
    > END


    You have not specified how to handle the case of the lastname's
    length is <= 9. Since you have not specified a value for those cases,
    you get NULL. Try
    Select First_Name, Last_Name, 'New_Name' = CASE
    WHEN LEN(Last_Name) > 9 Then Left(Last_Name, 9)
    else Last_Name -- Add this line.
    END

    > From Table1
    > Where? (what can I put in the where clause to remove all rows
    >with NULL in the 'New_Name' field)


    Wrong question.

    Do you want a list where all of the original lastnames were
    longer than 9, or do you want everything? For the latter, use the
    above, but if the former, use:
    Select First_Name, Last_Name, 'New_Name'=Left(Last_Name, 9)
    from table1
    where len(Last_Name)>9

    >Results Sample:
    >
    >First_Name Last_Name 'New_Name'
    >Joe Smith Null
    >Bobbie Miller Null
    >Carl Jeffersonn Jefferson


    Sincerely,

    Gene Wirchenko

  4. Re: Help: Remove Null from SQL Results

    Gene Wirchenko schreef op 11-05-2011 19:07:
    >> I have a query that is returning a null value in SQL 2008 and I cannot
    >> >seem to get rid of it. The query displays all 78000 rows, but I only
    >> >need it to display about 2000 rows. Basically, I am looking for all
    >> >last names that are greater than 9 characters and using the left
    >> >function to only return the first 9 characters. So, XYZABCKJL123
    >> >should return XYZABCKJL in a new column I created called New_Name.
    >> >Thank you for anyone who can help with this, I spent over 3 hours
    >> >searching online and nothing has seemed to work.
    >> >
    >> >
    >> >Select First_Name, Last_Name, 'New_Name' = CASE
    >> > WHEN LEN(Last_Name)> 9 Then Left(Last_Name, 9)
    >> > END

    > You have not specified how to handle the case of the lastname's
    > length is<= 9. Since you have not specified a value for those cases,
    > you get NULL. Try
    > Select First_Name, Last_Name, 'New_Name' = CASE
    > WHEN LEN(Last_Name)> 9 Then Left(Last_Name, 9)
    > else Last_Name -- Add this line.
    > END
    >

    No, he says that he's getting 78000 rows but he only wants about 2000
    rows. "I am looking for all last names that are greater than 9 characters"
    That's why

    WHERE LEN(IsNull(Last_name, '')) > 9

    as proposed by Michael is the correct solution. In your solution, he's
    gonna get all 78000 rows.

    Best,
    Henk

  5. Re: Help: Remove Null from SQL Results

    On Wed, 11 May 2011 20:09:23 +0200, Henk van den Berg
    wrote:

    >Gene Wirchenko schreef op 11-05-2011 19:07:
    >>> I have a query that is returning a null value in SQL 2008 and I cannot
    >>> >seem to get rid of it. The query displays all 78000 rows, but I only
    >>> >need it to display about 2000 rows. Basically, I am looking for all
    >>> >last names that are greater than 9 characters and using the left
    >>> >function to only return the first 9 characters. So, XYZABCKJL123
    >>> >should return XYZABCKJL in a new column I created called New_Name.
    >>> >Thank you for anyone who can help with this, I spent over 3 hours
    >>> >searching online and nothing has seemed to work.
    >>> >
    >>> >
    >>> >Select First_Name, Last_Name, 'New_Name' = CASE
    >>> > WHEN LEN(Last_Name)> 9 Then Left(Last_Name, 9)
    >>> > END

    >> You have not specified how to handle the case of the lastname's
    >> length is<= 9. Since you have not specified a value for those cases,
    >> you get NULL. Try
    >> Select First_Name, Last_Name, 'New_Name' = CASE
    >> WHEN LEN(Last_Name)> 9 Then Left(Last_Name, 9)
    >> else Last_Name -- Add this line.
    >> END
    >>

    >No, he says that he's getting 78000 rows but he only wants about 2000
    >rows. "I am looking for all last names that are greater than 9 characters"
    >That's why
    >
    >WHERE LEN(IsNull(Last_name, '')) > 9
    >
    >as proposed by Michael is the correct solution. In your solution, he's
    >gonna get all 78000 rows.


    Which one? I posted two solutions. With the first one, he will
    get the 78000. With the second, he will not (unless he has NULL
    Last_Names in which case I do not know what will happen).

    Sincerely,

    Gene Wirchenko

  6. Re: Help: Remove Null from SQL Results

    Gene Wirchenko schreef op 11-05-2011 22:19:
    >>> You have not specified how to handle the case of the lastname's
    >>> >> length is<= 9. Since you have not specified a value for those cases,
    >>> >> you get NULL. Try
    >>> >> Select First_Name, Last_Name, 'New_Name' = CASE
    >>> >> WHEN LEN(Last_Name)> 9 Then Left(Last_Name, 9)
    >>> >> else Last_Name -- Add this line.
    >>> >> END
    >>> >>
    >> >No, he says that he's getting 78000 rows but he only wants about 2000
    >> >rows. "I am looking for all last names that are greater than 9 characters"
    >> >That's why
    >> >
    >> >WHERE LEN(IsNull(Last_name, ''))> 9
    >> >
    >> >as proposed by Michael is the correct solution. In your solution, he's
    >> >gonna get all 78000 rows.

    > Which one? I posted two solutions. With the first one, he will
    > get the 78000. With the second, he will not (unless he has NULL
    > Last_Names in which case I do not know what will happen).
    >


    Based on his question (only those with lastname greater than 9
    characters) he should use

    SELECT First_Name, Last_Name, 'New_Name' = LEFT( Last_Name, 9 )
    WHERE LEN( ISNULL( Last_Name, '' ) ) > 9

    Unfortunately, I overlooked the first of your two solutions, because I
    focused on "greater than 9 characters".

    Best,
    Henk

  7. Re: Help: Remove Null from SQL Results

    On Thu, 12 May 2011 00:20:57 +0200, Henk van den Berg
    wrote:

    [snip]

    >Unfortunately, I overlooked the first of your two solutions, because I
    >focused on "greater than 9 characters".


    My first solution is the one that will return 78000 rows!

    Sincerely,

    Gene Wirchenko

  8. Re: Help: Remove Null from SQL Results

    Gene Wirchenko schreef op 12-05-2011 01:05:
    > On Thu, 12 May 2011 00:20:57 +0200, Henk van den Berg
    > wrote:
    >
    > [snip]
    >
    >> Unfortunately, I overlooked the first of your two solutions, because I
    >> focused on "greater than 9 characters".

    >
    > My first solution is the one that will return 78000 rows!
    >

    Yeah, I know. What's the reason you're restating that?

    > Sincerely,
    >
    > Gene Wirchenko



  9. Re: Help: Remove Null from SQL Results

    On May 11, 7:29*pm, Henk van den Berg wrote:
    > Gene Wirchenko schreef op 12-05-2011 01:05:> On Thu, 12 May 2011 00:20:57+0200, Henk van den Berg
    > > *wrote:

    >
    > > [snip]

    >
    > >> Unfortunately, I overlooked the first of your two solutions, because I
    > >> focused on "greater than 9 characters".

    >
    > > * * * My first solution is the one that will return 78000 rows!

    >
    > Yeah, I know. What's the reason you're restating that?
    >
    > > Sincerely,

    >
    > > Gene Wirchenko


    Hi, thank you for everybodys input, i am going to try these solutions
    first thing tommorow morning.

  10. Re: Help: Remove Null from SQL Results

    On Thu, 12 May 2011 01:29:25 +0200, Henk van den Berg
    wrote:

    >Gene Wirchenko schreef op 12-05-2011 01:05:
    >> On Thu, 12 May 2011 00:20:57 +0200, Henk van den Berg
    >> wrote:
    >>
    >> [snip]
    >>
    >>> Unfortunately, I overlooked the first of your two solutions, because I
    >>> focused on "greater than 9 characters".

    >>
    >> My first solution is the one that will return 78000 rows!
    >>

    >Yeah, I know. What's the reason you're restating that?


    Go up-thread to your original reply to me, and reread your
    answer. That ought to make it obvious.

    Sincerely,

    Gene Wirchenko

+ Reply to Thread
Page 1 of 2 1 2 LastLast