560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
A string of text is any series of alphanumeric characters. Here are some examples of strings:
This part of the tutorial shows how you can manipulate strings using SQL.
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
+ ' won ' +
CAST(FilmOscarWins AS varchar(2)) +
' Oscars' AS Film
FilmOscarWins > 0
The output from this would be as follows:
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.
There are 3 ways that you can convert numbers to text in SQL (we've blogged on dates separately):
|CAST||CAST(3.14159 AS varchar(10))||3.14159|
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.
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!|
|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.
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).
Here's a bit of SQL to find the last word in each film synopsis in a table:
Here are the stages this would go through for a given film synopsis.
|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)|
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.
You can use the following functions to repeat text:
|Function||What it does||Example||Would show|
|Space||Repeats spaces||'Wise' + SPACE(5) + 'Owl'||Wise Owl|
You can use the QuoteName function with a variety of arguments to enclose text in quotes or brackets. Here are some examples:
If you miss out the second argument, SQL will encase the first argument in square brackets.
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):
-- start with first letter in upper case
-- add on remainder in lower case
The output of this would be Wilfred.
|Parts of this blog|
25 Aytoun Street