How to do calculations and expressions in T-SQL
Part three of a five-part series of blogs

You can use SQL to do everything from simple arithmetic through to complicated functions - this blog gives you the low-down!

  1. Formulae, Expressions and Calculations in SQL
  2. Arithmetic and Numerical Functions
  3. Working with text (including string functions) (this blog)
  4. CASE WHEN statement
  5. Dealing with nulls - ISNULL, COALESCE and CASE

This blog is part of our full SQL online tutorial.  You can learn how to write SQL on a Wise Owl SQL classroom course if you're in the UK.

Posted by Andy Brown on 11 December 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Working with text (including string functions)

A string of text is any series of alphanumeric characters.  Here are some examples of  strings:

  • Wise Owl Business Solutions
  • (01457) 858877

This part of the tutorial shows how you can manipulate strings using SQL.

Converting and Concatenating Strings

You can use the + symbol to join bits of text together, and (as shown below) the CAST function to convert numbers or dates to text.  For example:

-- show films, with Oscars won

SELECT

FilmName

+ ' won ' +

CAST(FilmOscarWins AS varchar(2)) +

' Oscars' AS Film

FROM

tblFilm

WHERE

FilmOscarWins > 0

The output from this would be as follows:

Films with Oscars won

We needed the CAST function to force the number to be treated as text.

 

Note that if you want spaces to appear within concatenated text, you must include them within quote marks.

Functions to Convert Text

There are 3 ways that you can convert numbers to text in SQL (we've blogged on dates separately):

Function Example Would show
CAST CAST(3.14159 AS varchar(10)) 3.14159
CONVERT CONVERT(varchar(10), 3.14159) 3.14159
STR  STR(3.14159,4,2)  3.14

For the STR function, the second argument shows how long the final number will be, and the third argument shows how many decimal places it will have. 

Functions to Search for and Replace Text

Here are the main functions for finding and replacing text:

Example Shows What it does
CHARINDEX('owl','Wise Owl') 6 Shows where one string of text appears within another (owl starts at the 6th character).
REPLACE('Wise Owl','w','XX') XXise OXXl Replaces one string of text with another wherever it appears.
STUFF('Wise Owl',3,2,'nning') Winning Owl Puts one bit of text within another at a given position (here 3), replacing a certain number of characters (here 2).  My favourite function name!
PatIndex(
'%o_l','Wise Owl')		 6 Shows the first appearance of one wildcard pattern within another (% represents any string of characters, while the _ underscore character represents any single character).  See our criterias blog for more on wildcards.

Here's an example of how to use the REPLACE function to work out how many times a given word appears in a string of text:

-- number of times a given word appears

DECLARE @num int

 

DECLARE @word varchar(10)

SET @word = 'the'

 

-- before and after removing word

DECLARE @before varchar(MAX)

DECLARE @after varchar(MAX)

 

SET @before =

'The owl perched on the burning mast whence all but he had fled'

SET @after = REPLACE(@before, @word, '')

 

-- take difference in lengths of before/after, and

-- divide by length of word being sought to get

-- the number of replacements

SET @num = (LEN(@before) - LEN(@after))/len(@word)

SELECT 'Number found is ' + CAST(@num AS varchar(10))

Although this may use concepts you haven't seen before (such as variables), it should be reasonably clear how we're using the string functions involved.

Functions used for extracting text

If you want to extract one bit of text from another at a given position, you'll find these functions useful:

Example Shows What it does
LEFT('Wise Owl',4) Wise Takes a string of characters from the extreme left of the given bit of text.
RIGHT('Wise Owl',3) Owl Takes a string of characters from the extreme right of the given bit of text.
SUBSTRING('Wise Owl',3,5) se Ow Picks out the (here) 5-character string starting at (here) character 3.
LEN('Wise Owl') 8 Finds the length of a string of text (often useful for working out how many characters to extract).
REVERSE('Wise Owl') lwo esiW Reverses text (sometimes useful for extracting text, as in the example below).

Notice that the Substring function numbers characters from 1, and not 0 (as is the case in almost every other Microsoft instance of this function).

Worked example of various string functions

Here's a bit of SQL to find the last word in each film synopsis in a table:

REVERSE(

LEFT(

REVERSE(FilmSynopsis),

CHARINDEX(' ',REVERSE(FilmSynopsis))-1)

)

Here are the stages this would go through for a given film synopsis.

Stage Text
FilmSynopsis Ex-nun teaches father to love his children (and her).
REVERSE(this) .)reh dna( nerdlihc sih evol ot rehtaf sehcaet nun-xE
CHARINDEX(' ', this) 6 (where the first space appears)
LEFT(REVERSE(FilmSynopsis), this-1) .)reh
REVERSE(this) her).

Trimming Text

Trimming text involves removing leading and trailing blanks.  There isn't a single function to do this in SQL, but you can combine two to achieve the effect:

Function What it does Example Would equal
LTRIM Removes leading blanks LTRIM('  Owl') Owl
RTRIM Removes trailing blanks RTRIM('Owl  ') Owl

So you could use either of the expressions LTRIM(RTRIM('  Wise Owl  ')) or RTRIM(LTRIM('  Wise Owl  ')) to give the words Wise Owl, with no leading or trailing spaces.

Repeating Text

You can use the following functions to repeat text:

Function What it does Example Would show
Replicate Repeats text REPLICATE('Owl',3) OwlOwlOwl
Space Repeats spaces 'Wise' + SPACE(5) + 'Owl' Wise     Owl

Encasing text in quotation marks or parentheses

You can use the QuoteName function with a variety of arguments to enclose text in quotes or brackets.  Here are some examples:

Example Would return
QuoteName('Owl') [Owl]
QuoteName('Owl','''') 'Owl'
QuoteName('Owl','"') "Owl"

If you miss out the second argument, SQL will encase the first argument in square brackets.

Changing the case of text

You can change text to upper and lower case as follows:

What to do Function Example Would equal
Convert to upper case UPPER UPPER('Wise Owl') WISE OWL
Convert to lower case LOWER LOWER('Wise Owl') wise owl

By default SQL isn't case-sensitive, so these functions are less useful than their equivalents might be in VB or C#, for example.

Here's a way to capitalise the first letter of a string of text (this effect is often achieved by using the PROPER function, but this doesn't exist in SQL):

SELECT

 

-- start with first letter in upper case

UPPER(LEFT('wilFrEd',1)) +

 

-- add on remainder in lower case

LOWER(RIGHT('wilFrEd',LEN('wilFrEd')-1))

The output of this would be Wilfred. 

  1. Formulae, Expressions and Calculations in SQL
  2. Arithmetic and Numerical Functions
  3. Working with text (including string functions) (this blog)
  4. CASE WHEN statement
  5. Dealing with nulls - ISNULL, COALESCE and CASE
This blog has 0 threads Add post