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)

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.