SQL Server Reference Guide

Design Elements Part 11: String Manipulation

Last updated Mar 28, 2003.

In the tutorials that precede this one, I’ve been laying it on heavily for the topics of mathematical and statistical formulas, functions and algorithms (finally, I can hear you say) and continuing our discussion of design elements.

In this tutorial, I'll introduce one of the most useful and rich set of functions: those that deal with string manipulation. You'll find that you use these functions quite often in your development efforts.

I won't cover all the possible string functions, but I will focus on some of the more practical uses. You can take what you learn here and move forward with the information in the links I’ll provide at the bottom of this tutorial.

You had to create the statistical algorithms I explained in the last few tutorials by layering various functions and so on together. Luckily, it's easier for string manipulation. SQL Server has several string functions built right in.

What's a String, Anyway?

A string is defined as a set of character data. Computers can't read text, of course, so what we're really talking about here are numbers that represent characters: ASCII codes. ASCII (the American Standard Code for Information Interchanges) is a seven-bit code that was first proposed by the ANSI in 1963, and finalized in 1968. It's remained the computer standard ever since, and has been extended to cover other languages. This will become important as you think about how you want to interact with strings.

I’ll show a few of these codes in a couple of the functions a bit later, and I’ll explain how they impact some of the other functions as well.

There are a lot of ways to manage strings, so I'll attack this subject by categorizing the tasks you might want to accomplish. I'll demonstrate most of the functions with a SELECT statement, but of course they work with other T-SQL verbs as well.

Character Positions

I'll start the discussion with string positions. While it might not seem like a logical place to start, knowing the location of a string or character is the basis for some of the other functions I’ll explain later.

Essentially this functionality returns a number that shows the offset, or number of characters, from the start of some text where a particular character or string is located. You need this number because some of the other functions require it [md] and because the string or character we're looking for might not be the whole field. In other words, to capitalize a word, or locate text to do something else with it, you almost always need to know where it is within the larger text block.

I’ll start with the CHARINDEX function that will find the character you’re looking for. Here is the function and how it is used:

CHARINDEX(x, y): Returns the starting position of x in y 

This function locates the number where a string or character starts in a column. The following example uses the (very old) pubs database, since that should be available no matter what version of SQL Server you’re running. Sure, you can also just use a variable, but this is more realistic:

USE pubs
GO
SELECT CHARINDEX('a', au_fname) 
FROM authors
WHERE au_fname = 'Marjorie'
----------------------------------------
2

Notice that I've limited the results to the name Marjorie.

The CHARINDEX() function needs a full string to work with, while another function, PATINDEX(), can use wildcards. I’ll cover that function in another tutorial.

Now that you know where a character starts, It’s also useful to know the length of the string starting from that character from there. This comes in handy later as well:

LEN(x): Returns length of x

And here's an example of how you can use this one:

SELECT LEN(au_fname)
FROM authors
WHERE au_fname = 'Marjorie'
----------------------------------------
8

String Combinations and Breaks

Now that you know the position numbers and lengths of strings, you can use them to create, locate or combine strings in new ways. The first one I’ll focus on is SUBSTRING:

SUBSTRING(x, y, z): Returns the part of a string x, that starts at
position y for z number of characters

Here's an example that starts with the letter “h” in White, and then returns one position from that:

SELECT SUBSTRING(au_lname, 2, 1) 
FROM authors
WHERE au_lname = 'White'
---------------------------------------
i

Notice that what I've asked for is the au_lname column, starting two positions in, one character long. This is useful for grabbing the “middle” parts of text (even though it isn’t in the middle). The next function works from the right:

RIGHT(x, y): Returns the rightmost characters of x specified by
y

Using this function, you can get the string to the right of the starting character. Here's an example of that in action:

SELECT RIGHT(au_fname, 4)
FROM authors
WHERE au_fname = 'Marjorie'
----------------------------------------
orie

And Of course, there's a corresponding LEFT function:

SELECT LEFT(au_fname, 4)
FROM authors
WHERE au_fname = 'Marjorie'
----------------------------------------
Marj

Now that you know how to grab hunks of text, you need to learn how to connect those parts. For that, you simply use the “plus” sign:

+: Concatenates strings 

The + operator joins two strings together. Here's an example:

SELECT au_fname + au_lname
FROM authors
WHERE au_fname = 'Marjorie'
----------------------------------------
MarjorieGreen

This is another popular string function, but be careful; it’s also the math addition operator. Joining a numeric field and a character field in this way will fail. If you need to do that implicitly, you would need to bring in the CAST() or CONVERT() functions I explained in the data type tutorial.

String Conversions

Next I'll explain how to convert one string into another. The first function that does that is REPLACE:

REPLACE(x, y, z): Replaces all y's with z's in x

This function is a lot of fun. Here it is in action:

SELECT REPLACE(au_fname, 'a', 'zzzz')
FROM authors
WHERE au_fname = 'Marjorie'
----------------------------------------
Mzzzzrjorie

Here’s where the ASCII numbers I mentioned earlier comes in to play, using the function CHAR:

CHAR(): Converts ASCII to a character 

This function is used quite often to force SQL to display output on more than one line. You can use the ASCII value for a carriage-return to show the data on two lines:

SELECT au_fname + CHAR(13) + au_lname
FROM authors
WHERE au_fname = 'Marjorie'
----------------------------------------
Marjorie
Green

The query should output to text for this to work. The grid doesn't have return characters. Other useful ASCII codes are 9 (Tab) and 10 (Line Feed).

Next, if you need to change an upper or lower cased letter to another case, you have two functions available:

LCASE() or LOWER(): Converts strings to lower case
UCASE() or UPPER(): Converts strings to upper case

These two are pretty obvious, but they can be a lifesaver if the database is set to case-sensitive. LCASE and UCASE are generally the best way to compare strings in a WHERE clause.

String Compression and Expansion

Now I'll move on to making strings longer or shorter. Many times you will want to remove the spaces from a string to format it to the screen or output in a more “natural” way. You have two functions to do that:

LTRIM(): Removes leading spaces
RTRIM(): Removes trailing spaces

Sometimes an application pads the data it sends with spaces. As you’ve probably learned, spaces aren't "nothing," they are an ASCII value. To remove these spaces, you can "trim" them out. While many programming languages have a full TRIM function, T-SQL has both a right and left trim, and you can combine them to remove spaces on both sides of a string.

Here's an example:

SELECT '**' + RTRIM(LTRIM(' Buck ')) + '**'
----------------------------------------
**Buck**

Notice that for this example I've placed a couple of asterisks out to the side to show where the spaces were truncated.

Sometimes you want to put spaces in, not take them out. For that, you can use the SPACE function:

SPACE(x): Returns x spaces

So, what if we want to put spaces in rather than take them out? The SPACE() function does just that:

SELECT SPACE(5) + 'Buck' 
----------------------------------------
           Buck

String Creation

Now I'll show you how to create strings.

REPLICATE(x, y): Repeats x y times

I explained this cool function in the statistics tutorials. Here it is again:

SELECT REPLICATE('Hello ', 5)
----------------------------------------
Hello Hello Hello Hello Hello

(I love those old Three Stooges movies!)

Well, I've only touched on the basics, but you have plenty here to get you started. In future tutorials, I’ll use these simple constructs to do some pretty amazing things in code.

InformIT Articles and Sample Chapters

Strings are covered in more depth in SQL Server String, Cursor, Security and Rowset Functions, by Baya Pavliashvili.

Books and eBooks

You can learn a lot more about strings in Sams Teach Yourself SQL in One Hour a Day, 5th Edition, by Ryan Stephens, Ron Plew, and Arie D. Jones.

Online Resources

Here's one of my favorite discussion boards where someone has asked about the string functions and how they can help increment a value.