TRIM() in T-SQL

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714637

    Comments posted to this topic are about the item TRIM() in T-SQL

  • DBA_Rob

    Default port

    Points: 1492

    Steve,
    Do you have an error in the first query under the 'Choosing what gets Trimmed' section?  Currently it shows the query as:
    SELECT Result = '[' + TRIM(n) + ']' ,
                  FirstLetter
    = ASCII(SUBSTRING(TRIM(n), 1, 1)),
                  LastLetter
    = ASCII(SUBSTRING(TRIM(n), LEN(TRIM(n)) - 1, 1))
    FROM( VALUES (',Broncos, Raiders, Chargers, Chiefs ') ,
                                 
    (',Texans, Jaguars, Colts, Titans, ' ) ,
                                 
    ('Steelers,Ravens,Browns,Bengals,') ,
                                 
    (',Patriots,Dolphins,Bills,Jets'))
    AS a (n);
    GO

    And the results show rows with missing commas at the beginning and end, but the actual TRIM commands "TRIM(n)"does not list that commas should be removed (which I think is what you intended).

    An interesting new feature in SQL Server though, thanks for the article highlighting that it finally exists.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714637

    Yep. Thanks, forgot the TRIM(',' FROM n) part.

  • Alan Burstein

    SSC Guru

    Points: 61019

    I must admit, when I saw the title, TRIM() in T-SQL my first thought was, "boring!" 
    That said - I was quite impressed and found the article to be quite informative. Great work Steve.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

  • MMartin1

    One Orange Chip

    Points: 27488

    AS I dont have version 2017, will it remove two leading or trailing commas instead of one as you showed in your example. Would it Remove two leading 'patriots' in the string?
    Thanks, great article

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714637

  • HansVE

    Mr or Mrs. 500

    Points: 524

    "I don't know why they made this decision instead of structuring things like this..."
    Because ANSI SQL.

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

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