BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
You can use SQL to do everything from simple arithmetic through to complicated functions - this blog gives you the low-down!
- Formulae, Expressions and Calculations in SQL
- Arithmetic and Numerical Functions
- Working with text (including string functions) (this blog)
- CASE WHEN statement
- 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:
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.
- Formulae, Expressions and Calculations in SQL
- Arithmetic and Numerical Functions
- Working with text (including string functions) (this blog)
- CASE WHEN statement
- Dealing with nulls - ISNULL, COALESCE and CASE