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.