Post reply

LEN() anamoly

  • July 19, 2005 at 12:44 pm

    #165393

    I have been using SQL for many, many years.  This problem seems so simple but I am stumped.

    I need to conditionally add a zero to the front of values in a field.  Prior to finalizing the UPDATE statement, I tested the filter with a SELECT statement.  The results have me baffled.  (Pasting and editing the statements for simplicity so I could introduce small errors.)

    1) The SELECT statement...

    SELECT TOP 100 Store, LEN(Store) AS TrimLen

    FROM Temp

    A store value such as 307837 yields a length of 7, 8 or 9, for instance.  Similar unexpected results occur.

    2) I tried trimming left and then trimming right too and the REPLACE function just for grins...

    SELECT TOP 100 Store, LEN(LTRIM(RTRIM(Store))) AS TrimLen, LEN(REPLACE(Store,' ','')) AS ReplLen

    FROM Temp

    Same odd results.

    3) I pasted the result into an editor that would allow me to see funky characters.  Nothing but numbers and spaces.

    4) Then I copied and pasted a store value from the result into single quotes as seen in the following...

    SELECT LEN('307837  '), LEN(RTRIM('307837  ')), LEN(REPLACE('307837  ',' ',''))

    As I would expect, this yields 6 for each function.

     

    What in the world is going on here?  Why do I not get 6 in the first query?

    Suggestions please.

    Thanks.

     

  • July 19, 2005 at 12:50 pm

    #575620

    Can you post the definition of the table TEMP ?

     


    * Noel

  • July 19, 2005 at 12:54 pm

    #575622

    The Store field is varchar, length 20.  I have a handful of other fields, which I expect are irrelevant but will certainly post as needed.

  • July 19, 2005 at 1:03 pm

    #575623

    I believe you have char(10) + Char(13) attached at the end of those.

    Can you post what does

    select cast(store as varbinary(20))

    returns

     


    * Noel

  • July 19, 2005 at 1:07 pm

    #575625

    A small sample...

    field list = store, LEN(Store), cast(store as varbinary(20))

    2957684   9 0x323935373638340D0D

    2957535 7 0x32393537353335

    1814100  8 0x313831343130300D

    1814008  8 0x313831343030380D

  • July 19, 2005 at 1:24 pm

    #575630

    Like I suspected 0D in Hex is 13 in decimal therefore you have Char(13) embeded in the field !!!

    so you can :

    select len(replace(store, char(13),''))

    and Enjoy

     


    * Noel

  • July 19, 2005 at 1:37 pm

    #575634

    Noel, thanks much.

    I sure didn't expect that.  And I would have expected my editor to show that CHAR(13).

    'ppreciate it.

  • July 19, 2005 at 1:41 pm

    #575637

    happy to help

     

     


    * Noel

  • July 20, 2005 at 2:52 pm

    #575920

    Nice trick displaying it as a varbinary, I'll have to remember that.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

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

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