SQL Server - Azure - DotNet - Programming Courses and More - SQLNetHub

Segmenting Strings in SQL Server

by

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.

 

Examples of Segmenting Strings 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!

 

Learn More Tips like this – Enroll to the Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Upgrade your Tech Skills – Learn all about Azure SQL Database

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 2,411