Thursday, December 16, 2010

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!


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!
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (