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:
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:
DECLARE @num int
DECLARE @word varchar(10)
SET @word = 'the'
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, '')
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
UPPER(LEFT('wilFrEd',1)) +
LOWER(RIGHT('wilFrEd',LEN('wilFrEd')-1))
The output of this would be Wilfred.
