Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Function appears to affect all rows not just those that meet condition

    Hi

    below sample data incoming from a source that cannot be changed. Please ignore the mishandling of zls. Obviously it is not insurmountable - I am just interested in why it is happening because I cannot explain it.

    Code:
    
    DECLARE @t TABLE
    (the_data CHAR(73))
     
    
    SET DATEFORMAT dmy
    SET NOCOUNT ON
     
    
    
    INSERT INTO @t
    SELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'0'+space(10)+'08 01 2002'+space(10)+'04 10 2002'
    UNION ALL
    SELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'109 08 2004'+space(20)+'21 07 2005'
    UNION ALL
    SELECT ' 11 13026721XX198734 1'+space(10)+'0'+space(10)+'XXXXXXXXXX'+space(10)+'09 01 2003'
     
    
    
    SELECT CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX',NULL) AS SMALLDATETIME) AS date_1_prob
    ,CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX','') AS SMALLDATETIME) AS date_1_ok_ish
    ,CAST(NULLIF(REPLACE(date1_text,' ','/'),'XXXXXXXXXX') AS SMALLDATETIME)AS date_1_fine
    , date1_text
    FROM--derived table - selecting relevant substring
    (SELECT LTRIM(RTRIM(SUBSTRING(the_data, 44, 10))) AS date1_text
    FROM @t)AS der_t
    
    


    Code:
     
    date_1_prob             date_1_ok_ish           date_1_fine             date1_text
    ----------------------- ----------------------- ----------------------- ----------
    NULL                    2002-01-08 00:00:00     2002-01-08 00:00:00     08 01 2002
    NULL                    1900-01-01 00:00:00     1900-01-01 00:00:00 
    NULL                    1900-01-01 00:00:00     NULL                    XXXXXXXXXX

    Can anyone explain the result in the first row first column?

    Thanks
    Last edited by pootle flump; 01-31-07 at 06:24.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    either you are doing something different from what you posted, or i'm going nuts

    SUBSTRING(the_data, 44, 10) gives spaces, spaces, '003' respectively for the data you posted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I posted what I ran but what is on the site is not that. Crappy pants - spaces all disappeared. Will sort out. Each string should be 73 chars in length.

    Apols
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    original post edited & tested.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    It looks like REPLACE will return NULL when either of the parameters is NULL (not documented in the BOL though).

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is most definitely very weird, i am getting the same result, and i don't understand it either

    REPLACE(date1_text,' ','/') works fine, let's call this T1

    the culprit seems to be REPLACE(T1,'XXXXXXXXXX',NULL)

    try using REPLACE(T1,'XXXXXXXXXX','') and you will see something different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    that is most definitely very weird, i am getting the same result, and i don't understand it either

    REPLACE(date1_text,' ','/') works fine, let's call this T1

    the culprit seems to be REPLACE(T1,'XXXXXXXXXX',NULL)

    try using REPLACE(T1,'XXXXXXXXXX','') and you will see something different
    Yeppers - tried that and found it fine in the date_1_ok_ish field. NULLIF works fine too. Just, as you say, the second replace is an oddity.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    I think "weird" is a big word for a lack of documentation

    But, a quote from the 2005 BOL (on REPLACE ofcourse):
    Code:
    Returns NULL if any one of the arguments is NULL.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not in 2k BoL.

    Well - I never knew that - NULL even when the condition is not met. Thanks for the answer
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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