Post reply

quirk of nulls & blanks using aggregate function

  • June 30, 2008 at 12:44 am

    #129799

    hi guys,

    any help appreciated.

    i have created (in t-sql) an entity ‘Client’ which includes two columns declared as nullable - CL_TITLE and CL_GENDER. i intend to perform some data cleansing based on what I find in these columns. blank or null values are giving me some grief however.

    i have performed a record count (sum) grouping by these two columns. there were two rows in my aggregate result set that confused me:-

    •CL_TITLE = (blank); CL_GENDER = (blank); Count = 172

    •CL_TITLE = NULL; CL_GENDER = NULL; Count = 19

    to investigate further what unprintable characters may be contained in the aggregate row containing blanks, i added a varbinary conversion of both fields to the query. the result set confused me further.

    where both columns had (blank), the hex conversion value was ‘0x’. and where both columns had NULL values, the hex conversion value was NULL.

    according to my ascii chart, ‘0x’ IS NULL.

    is there some quirky little database setting i need to tinker with?

    cheers

    tim

  • July 1, 2008 at 7:18 am

    #836658

    Hi Tim

    I use the ASCII function for this, usually the first two characters of the column...

    DECLARE @tempvar VARCHAR(5)

    SET @tempvar = CHAR(10) + CHAR(13)

    SELECT ASCII(LEFT(@tempvar,1)), ASCII(SUBSTRING(@tempvar,2,1))

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 21, 2008 at 1:25 am

    #845574

    indeed the ascii of null and blank evaluate to the same. i'll use this. thanks for that.

Viewing 3 posts - 1 through 3 (of 3 total)

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