Remove the last character in a string in T-SQL?

sql

string-manipulation

null-handling

best-practices

byAlex Kataev·Aug 8, 2024

When your adventures in T-SQL land you in the need to trim off the final character from a sting:

SELECT LEFT(column, LEN(column) - 1) AS trimmed
FROM table;

Peel off column and table labels and slap on your actual column and table names. And, bam! You're done.

Tackling NULLs and edge cases

Working with strings is not all rainbows 🌈 and unicorns 🦄. NULL values can leap out of nowwhere causing a ruckus in your query results. Here's how you write the equivalent of a "I have an unhealthily high self-esteem" pep talk for your strings:

SELECT CASE 
         WHEN column IS NOT NULL AND LEN(column) > 0 
         THEN LEFT(column, LEN(column) - 1) 
         ELSE NULL 
       END AS trimmed
FROM table;

Preventing NULL value freak-outs is just one of those T-SQL best practices, make sure you've got it under your belt.

Re-use friendly for coding magnates

If you're a coding magnate with starry-eyed dreams of reusability, here's a function for the job:

-- An investment so good, you can use it again and again
CREATE FUNCTION dbo.RemoveLastChar (@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN CASE 
             WHEN @str IS NOT NULL AND LEN(@str) > 0 
             THEN LEFT(@str, LEN(@str) - 1) 
             ELSE NULL 
           END
END

Wrap that function around your queries like a cosy blanket:

SELECT dbo.RemoveLastChar(column) AS trimmed
FROM table;

The atypical family - alternatives to LEFT and LEN

Not a fan of LEFT and LEN? No worries, T-SQL's got you covered with a bunch of their lesser-known cousins who can do the job just as well:

REVERSE and STUFF: The cool kids on the block when dealing with variable-length strings or multi-occurence logic within a query.

  -- Reversing stuff and then stuffing it back in. It's cool, trust me.
  SELECT STUFF(column, LEN(column), 1, '') AS trimmed
  FROM table;

SUBSTRING: This one's an introvert - keeps to itself, but gets the job done with precision when asked to adjust lengths.

-- Like using a scalpel. Precise, clean cuts every time.
SELECT SUBSTRING(column, 1, LEN(column) - 1) AS trimmed
FROM table;

In all cases, the NULL and empty string check still applies. Yes, you gotta cover your bases.

Repetition is not your friend

If you're working with complex logic or recurrent computation within a query, avoid rehashing trimming logic. Use a CTE (Common Table Expression) or a subquery to condense it, like so:

-- Feed the squirrels! Use Ctrl+C, Ctrl+V sparingly.
WITH CTE AS (
    SELECT column, LEFT(column, LEN(column) - 1) AS trimmed
    FROM table
)
SELECT trimmed FROM CTE;

Clean queries are happy queries. Keep them tidy, make them smile.

Unwanted guests—empty strings

Empty strings are like ghosts in your code, causing inexplicable errors. Trail them with LEN and NULLIF:

-- "We ain't afraid of no ghosts!" - The StringBusters
SELECT NULLIF(LEFT(column, NULLIF(LEN(column) - 1, -1)), '') AS trimmed
FROM table;

This way, you're not trying to trim what's already bald, preserving the wholesomeness of data set.

Considerations for the big guys

Handling VARCHAR(MAX) or NVARCHAR(MAX) types? Remember, size does matter:

-- Like Hulk, but less green. Can handle big things, but transforms back.
DECLARE @longText VARCHAR(MAX) = '...'; -- Your long string here
SET @longText = LEFT(@longText, NULLIF(LEN(@longText) - 1, -1));