Post reply

checking length of string in a col

  • November 16, 2006 at 2:08 am

    #170975

    i am copying data from one table to another. some of the columns that are being transformed have larger varchar value than in the destination table. is there a sproc that i can run to see if any of the values in a col are longer than a specific value, or even just give me a list of the lengths of values in the column? i just want to check this before i alter my tables to match.

  • November 16, 2006 at 2:30 am

    #672207

    Use DATALENGTH, eg

    SELECT MAX(DATALENGTH([col])) FROM

    To find the longest length of the column

    SELECT [col] FROM WHERE DATALENGTH([col]) > n

    To list the columns longer than the maximum required

    (substitute n with the new column length)

    You stated varchar so all should be OK but beware of nvarchar as DATALENGTH will 2x the number of characters

    Also another note LEN will give you the length of the column but will ignore trailing spaces so you might get a truncataion error even if LEN reports the length less than the maximum

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 16, 2006 at 3:43 am

    #672211

    thanks for that, it helped me greatly. much appriciated

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

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