Bits of .NET

The Challenge: You have a column of full names, product titles, or long descriptions, and you need to grab just the first and last words. Since SQL doesn’t have a native Split() function that returns an array easily, we have to get creative with string positioning.

The Solution: We use a combination of CHARINDEX, SUBSTRING, and the clever REVERSE trick to pinpoint the boundaries of our target words.

🚀 The “Bulletproof” SQL Script

This script handles full sentences, single words, and even empty strings without breaking. Copy-paste this into your query window:

DECLARE @Sentence VARCHAR(MAX) = 'The quick brown fox jumps over the lazy dog';

-- Calculate indices for the first and last space
DECLARE @first_space_index INT = CHARINDEX(' ', @Sentence) - 1;
DECLARE @last_space_index  INT = CHARINDEX(' ', REVERSE(@Sentence)) - 1;

-- Logic: If no space is found, the whole string is both the first and last word.
IF @first_space_index < 0
    SELECT @Sentence AS [First Word], @Sentence AS [Last Word];
ELSE
    SELECT 
        SUBSTRING(@Sentence, 1, @first_space_index) AS [First Word], 
        REVERSE(SUBSTRING(REVERSE(@Sentence), 1, @last_space_index)) AS [Last Word];

🔍 The “Secret Sauce”: Why use REVERSE?

Finding the first word is easy (look for the first space from the left). But finding the last word is tricky because its position varies.

The Pro Trick: By REVERSE-ing the entire sentence, the last word becomes the first word. We grab it, and then REVERSE it back to its original form. It’s significantly faster and cleaner than writing complex loops!

SELECT FirstName = SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1)...

Summary

Mastering string boundaries is essential for any SQL developer. By using REVERSE, you’ve just added a high-performance tool to your T-SQL toolkit that handles dynamic data lengths with ease.