Post reply

nulls in a table

  • October 16, 2003 at 12:35 pm

    #82447

    How can i find where there are any nulls in a table.I dont want to check it on ecery column,I would like to check it on entire table.

    Thanks.

  • October 16, 2003 at 12:54 pm

    #478532

    You should check your table schema to find out which columns are allowed to be 'nullable'. There is a chance these columns have 'NULL' value.

  • October 16, 2003 at 1:10 pm

    #478535

    any query to get the result?

  • October 16, 2003 at 1:10 pm

    #478534

    any query get the result?

  • October 16, 2003 at 1:45 pm

    #478541

    Hi,

    Let's suppose you have a table called Table1 and the columns are Column1, Column2,Column3

    Column1Column2Column3

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

    1NULLtest1

    2test2NULL

    3test3test4

    SELECT * FROM Table1 WHERE column1 IS NULL OR Column2 IS NULL

    Then you will get the following

    Column1Column2Column3

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

    1NULLtest1

    2test2NULL

    Hope that's what you want.

    Thanks.

  • October 16, 2003 at 2:19 pm

    #478548

    He may have large number columns defined with nullable.

    Load result of sp_columns 'yourtable' into cursor, loop the cursor, find the columns with nullable defined, create dynamic select statement as showed by srgangu and execute it at end of loop.

  • October 16, 2003 at 2:56 pm

    #478555

    Run the following code produced from below:

    SELECT 'SELECT ''' + COLUMN_NAME + ''', COUNT(*) FROM ' + TABLE_NAME + ' WHERE ' + COLUMN_NAME + ' IS NULL;'

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE IS_NULLABLE = 'YES'

    AND TABLE_NAME = 'YOUR_TABLE_NAME'

    ORDER BY TABLE_NAME, ORDINAL_POSITION;

  • October 16, 2003 at 3:36 pm

    #478557

    In the case of the null able fields have blank, tab, line feed or even carriage return and a ' ' characters, the following code may be help:

    replace(replace(replace(replace(ltrim(rtrim(YourFieldName)),char(9),''),char(10),''),char(13),''),' ','')

  • October 16, 2003 at 3:44 pm

    #478558

    too bad, the square box could not be display here.

    replace(replace(replace(replace(ltrim(rtrim(YourFieldName)),char(9),''),char(10),''),char(13),''),'SQUARE BOX','')

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply