Commonly used string functions in SQL Server

In this article, we provide a list of commonly used string functions in SQL Server. By combining these functions, it is possible to easily manipulate strings.

For each function, we provide useful examples, that can help you better understand these functions’ usage.

 

REPLICATE

This function given a string and an integer N, it repeats N-times the given string

Example:

declare @string as varchar(10)
declare @num_repeats as int

set @string='ABCD '
set @num_repeats=4

select replicate(@string,@num_repeats)

 

Concatenation

By using the ‘plus’ (+) operator you can concatenate two or more strings

Example:

declare @string1 as varchar(10)
declare @string2 as varchar(10)

set @string1='Hello '
set @string2='World!'

select @string1 + @string2

 

REPLACE

This function given a string, a string pattern and a replacement string, it finds the given pattern in the string and replaces it with the replacement string

Example:

declare @string as varchar(20)
declare @pattern as varchar(10)
declare @replacement_string as varchar(10)

set @string='Hard drive'
set @pattern='drive'
set @replacement_string='disk'

-- Original Expression
select @string

-- Modified Expression
select REPLACE(@string,@pattern,@replacement_string)

 

SUBSTRING

This function given an expression, an integer pointing to the starting position and an integer representing the characters length, it returns the corresponding part of the expression

Example:

declare @expression as varchar(20)
declare @start_index as int
declare @length as int

set @expression='Hello_World'
set @start_index=1
set @length=5

-- Original Expression
select @expression

-- Modified Expression
select SUBSTRING(@expression ,@start_index,@length)

 

CHARINDEX

This function given a string and a pattern, it returns a pointer (int) to the starting position of the latter

Example:

declare @string as varchar(20)
declare @pattern as varchar(10)

set @string='SQL Server'
set @pattern='Server'

select CHARINDEX(@pattern,@string)

 

LEN

This function given a string, it returns its size in terms of number of characters

Example:

declare @string as varchar(20)

set @string='SQL Server'

select LEN(@string)

 

DATALENGTH

This function given an expression, it returns the number of bytes used

Example:

declare @expression as varchar(20)
set @expression='SQL Server'

select DATALENGTH(@expression)

 

ASCII

This function, given a character expression, it returns ASCII code value of its leftmost character.

Example:

declare @char_expression1 as char(1)
declare @char_expression2 as char(1)
declare @char_expression3 as char(1)

set @char_expression1='S'
set @char_expression2='Q'
set @char_expression3='L'

-- Get the ASCII code values for the characters 'S','Q', 'L'
select ASCII(@char_expression1)
select ASCII(@char_expression2)
select ASCII(@char_expression3)

 

CHAR

This function converts ASCII code values back to characters

Examples (based on the previous ASCII example):

declare @char_expression1 as char(1)
declare @char_expression2 as char(1)
declare @char_expression3 as char(1)

set @char_expression1='S'
set @char_expression2='Q'
set @char_expression3='L'

 

Get the ASCII code values for the characters ‘S’,’Q’, ‘L’ and then decode them back to characters:

select CHAR(ASCII(@char_expression1))
select CHAR(ASCII(@char_expression2))
select CHAR(ASCII(@char_expression3))

-- Construct the word!
select CHAR(ASCII(@char_expression1)) + CHAR(ASCII(@char_expression2)) + CHAR(ASCII(@char_expression3))

 

SPACE

This function given an integer N, it returns N blank spaces

Example (with concatenation):

declare @string1 as varchar(15)
declare @string2 as varchar(15)
declare @spaces as int

set @string1='Before_Spaces'
set @string2='After_Spaces'
set @spaces=10

-- Original Expression
select @string1 + @string2

-- Modified Expression
select @string1 + SPACE(@spaces) + @string2

 

LEFT

This function, given a string and an integer N, it returns the first N characters of the string counting from the left

Example:

declare @string as varchar(15)
declare @num_chars as int

set @string='Automobile'
set @num_chars=4

-- Original Expression
select @string

-- Modified Expression
select LEFT(@string,@num_chars)

 

RIGHT

This function, given a string and an integer N, ir returns the first N characters of the string counting from the right

Example:

declare @string as varchar(15)
declare @num_chars as int

set @string='Automobile'
set @num_chars=6

-- Original Expression
select @string

-- Modified Expression
select RIGHT(@string,@num_chars)

 

REVERSE

This function given a string, it reverses it

Example:

declare @string as varchar(15)
set @string='1234'

-- Original Expression
select @string

-- Modified Expression
select REVERSE(@string)

 

LOWER, UPPER

These two functions given a character expression, they set it to lowercase or uppercase respectively

Examples:

declare @expression1 as varchar(15)
declare @expression2 as varchar(15)

set @expression1='SQL SERVER'
set @expression2='sql server'

select LOWER(@expression1)
select UPPER(@expression2)

 

LTRIM, RTRIM

These two functions given a character expression, they remove the leading and trailing blank spaces respectively

Examples:

declare @expression as varchar(30)
declare @additional_string as varchar(15)

set @expression=' SQL SERVER '
set @additional_string=' 2008'

-- Original Expression
select space(10)+ @expression + space(10) + @additional_string

-- Modified Expressions
select LTRIM(space(10) + @expression + space(10)) + @additional_string
select RTRIM(space(10) + @expression + space(10)) + @additional_string

For more information on string functions in SQL Server, you can visit this MS Docs article.

 

 

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 (2 votes, average: 5.00 out of 5)

Loading...

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

© SQLNetHub

2 thoughts on “Commonly used string functions in SQL Server”

  1. Thanks Ioannis.

    The string functions is a very essential part of SQL Server. By using the available string functions, the developer can easily manipulate strings within the various processes developed in database applications.

    Also, by using combinations of the string functions, a database developer can implement various logics involving string manipulation.

    -Artemakis

Comments are closed.