Commonly used string functions in SQL Server

Commonly used string functions in SQL Server

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
In this post, I provide a list of commonly used string functions in SQL Server.
By combining these functions, it is possible to easily manipulate strings.

— 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 funtion 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 MSDN Library link.


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.

2 thoughts on “Commonly used string functions in SQL Server

  1. Artemakis Artemiou

    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