Segmenting Strings in SQL Server

Segmenting Strings in SQL Server

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.