none
How to get a longest cell's length

    Question

  • I want to get a longest cell's length in a field, but how?
    • Edited by Dynamo70 Saturday, May 11, 2013 7:23 AM wrong title words
    Saturday, May 11, 2013 7:21 AM

Answers

  • Hi, you can use MAX and DATALENGTH to achieve this.

    SELECT MAX(DATALENGTH(field1)) FROM Table1

    To include the field values in the query, you can enhance it a bit as follows:

    SELECT field1, MAX(DATALENGTH(field1)) as MaxLen
    FROM Table1
    GROUP BY field1
    ORDER BY MaxLen desc

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.



    Saturday, May 11, 2013 7:30 AM
    Moderator

All replies

  • Hi, you can use MAX and DATALENGTH to achieve this.

    SELECT MAX(DATALENGTH(field1)) FROM Table1

    To include the field values in the query, you can enhance it a bit as follows:

    SELECT field1, MAX(DATALENGTH(field1)) as MaxLen
    FROM Table1
    GROUP BY field1
    ORDER BY MaxLen desc

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.



    Saturday, May 11, 2013 7:30 AM
    Moderator
  • Thanks Samuel, it works well. Please tell me in Pervasive Sql  recompance because I have also Pervasive Sql
    Saturday, May 11, 2013 7:43 AM
  • I'm not familiar with Pervasive SQL syntax, but you may be able to look at their supported string functions to find the equivalent of MAX and DATALENGTH/LEN (if they are named differently).

    Best of luck!
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, May 11, 2013 7:51 AM
    Moderator