Thursday, January 15, 2009

Commonly used string functions in SQL Server

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.
[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

2 comments:

Ioannis Stavrinides said...

This was a very helpful post. Having everything readily available and with examples (!) makes a developers' life so much easier.

Artemakis Artemiou said...

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