Other: Trimming Field Lengths

Other: Trimming Field Lengths

Sometimes you may want to manually trim records so that they have certain lengths.

The scripts below provide examples on how to best do this.

  1. --This is the max length you want
  2. DECLARE @MaxLength INT = 50
  3.  
  4. --This is the characters you want for an ellipsis.
  5. --Notice that the default value is the ellipsis character
  6. --and not three periods. (File systems do not like trailing periods).
  7. DECLARE @Ellipsis NVARCHAR(MAX) = '…'
  8.  
  9. --We do math so that if we ellipsize, we still wont be over the max length.
  10. DECLARE @TrimTill INT = @MaxLength - LEN(@Ellipsis)
  11.  
  12. --Trim Contacts Full Name
  13. UPDATE
  14.             __M_Contacts
  15. SET
  16.             Final_FullName = CONCAT(SUBSTRING(Final_FullName, 1, @TrimTill), @Ellipsis)
  17. WHERE 1=1
  18.             AND LEN(Final_FullName) > @MaxLength
  19.  
  20. --Trim Matter Description
  21. UPDATE
  22.             __M_Matters
  23. SET
  24.             Final_Description = CONCAT(SUBSTRING(Final_Description, 1, @TrimTill), @Ellipsis)
  25. WHERE 1=1
  26.             AND LEN(Final_Description) > @MaxLength




    • Related Articles

    • Custom Field Kinds

      The following is a list of the valid Kinds of custom fields. Kind Notes TextLine TextArea HtmlArea Number Represents any kind of number. Integer Represents only whole numbers Money Date Time TimeSpan Represents durations. DateTime EmailAddress ...

    • Copy a Custom Field into a Core Field

      Some systems don't have certain core fields and clients often create a custom field that they use instead. The following script will let you copy values from a custom field named 'Opened' into __M_Matters.Final_Date_Opened . You can adjust this ...

    • Matters: Trimming Client Numbers from Matter Numbers

      The following script will peel off client numbers from matter numbers. For example, if a matter has a ReferenceCode like: ABC-0001 After the script runs, it will have a reference code of: 0001 Scenario 1: There are Client Reference Codes In some ...

    • Convert Any Field into a Picklist

      The following script lets you convert any field into a picklist. --This script will convert any field (usually text fields) into Picklists --and generate Picklist Options from the values. GO DECLARE @Ids Table(Id NVARCHAR(MAX)) INSERT INTO @Ids ...

    • Create a "Legacy Matter Number" Custom Field

      Some firms rely heavily on Matter Numbers to search for a specific matter. Sometimes when they migrate to a new system it can't replicate the automatic numbering system from their old system so they want to renumber all their matters to a new scheme ...