Segmenting Strings in SQL Server

Lately I have been dealing a lot with SQL Server development that involves among other, segmenting strings.

So, I decided to post an example-based article that discusses some simple ways of segmenting strings with T-SQL mainly by using the SUBSTRING built-in function and some other built-in functions such as CHARINDEX, LEFT, RIGHT and LEN.

Additionally, in one of my previous posts, I described the basic String built-in functions in SQL Server.

Enough theory, let’s see some examples!

Baseline

DECLARE @expression AS VARCHAR(20)

SET @expression='Hello World'

Original Expression

SELECT @expression

Requirement 1: Get the first word only

-- Methodology 1: Using the SUBSTRING built-in function

SELECT SUBSTRING(@expression,0,CHARINDEX(' ',@expression))

-- Methodology 2: Using the LEFT and CHARINDEX built-in functions

SELECT LEFT(@expression,CHARINDEX(' ',@expression))

Requirement 2: Get the second word only

-- Methodology: Using the SUBSTRING built-in function

SELECT SUBSTRING(@expression,CHARINDEX(' ',@expression)+1,(LEN(@expression)-CHARINDEX(' ',@expression)))

Requirement 3: Get the first letters of each word

-- Methodology: This is a little bit complex requirement and SUBSTRING is not enough.
-- We also need to use the CHARINDEX, LEN and LEFT built-in functions.

SELECT LEFT(@expression,1)+LEFT(SUBSTRING(@expression,CHARINDEX(' ',@expression)+1,(LEN(@expression)-CHARINDEX(' ',@expression))),1)

This post provided simple examples on how strings can be segmented in SQL Server. I did not get into complex scenarios as if I did that I am sure that I would need many hours for finishing the article as the scenarios can vary a lot!

If you have another string segmentation scenario and/or its solution feel free to add it by commenting this post!

I hope you found this post useful!

I'm reading: Segmenting Strings in SQL ServerTweet this!