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

    • 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 ...

    • Convert a Custom Field into a Practice Area

      --The system we are coming from does not have practice areas --however, the client has created a picklist custom field that mimics this. --We are going to convert this custom field into a practice area. --First, populate the practice areas table with ...