Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

SQL 101 - String Manipulation Functions

Shannon_Lowder
CERTIFIED EXPERT
Published:
Updated:
In a previous post I introduced you to calculated fields, and mentioned there were many other functions you could use to compute values.  It's time to introduce you to the eight frequently used functions when it comes to manipulating strings in SQL Server.

LEFT and RIGHT
If you ever need the first 3 or last 3 characters of a string, then you'll need to use LEFT or RIGHT  They both have similar syntax.
LEFT(string, length)
RIGHT(string, length)

Open in new window

The string can be anything that contains string data: a variable containing a VARCHAR or NVARCHAR, a literal VARCHAR or NVARCHAR, or a column with a type of VARCHAR or NVARCHAR, etc.  Are you sensing a pattern here?  You can't take the left 3 characters of an INT, or a DATETIME, or a TEXT field.  It may seem obvious why you can't do this operation on a number, but you may ask why you can't use these functions on a TEXT field.

The short answer is TEXT values are a very special case to SQL.  If you need to try to take the LEFT or RIGHT x characters of a number or text, first CAST or CONVERT that expression to a VARCHAR of sufficient size.  Hopefully you can create a VARCHAR or NVARCHAR big enough to hold your string.

The length is an INTEGER telling the server how many characters you want, using positive INTEGERS.
UPPER and LOWER
The syntax to UPPER and LOWER is simple too.  UPPER changes all the characters to uppercase, and LOWER returns all lower case.  You don't have to use these functions in comparisons.  Every SQL server I've used used a case insensitive collation for columns I searched through, but you may need these functions, as I try to avoid using the phrase always and never What this means is A is equal to a.  Also note, these functions only work on CHAR, NCHAR, VARCHAR, and NVARCHAR datatypes.
UPPER(string)
LOWER(string)

Open in new window

LTRIM and RTRIM
Whenever you deal with data that has come from free form fields, users will invariably add problematic data to the form.  It's not always intentional, but you as the database professional will have to clean it up.  One of the most common items you have to clean up is leading or trailing spaces. The only odd thing is, Microsoft hasn't built a method to do both at the same time.  But I can show you how, but I'll leave that for another post.

The syntax is simple.
LTRIM(string)
RTRIM(string)

Open in new window

You pass it a string (CHAR, NCHAR, VARCHAR, NVARCHAR), and it takes off either the leading spaces, or the trailing spaces.  You shouldn't need to use RTIM on a variable length character string.

REPLICATE
If you need to do the opposite of LTRIM or RTRIM and repeat a character a certain number of times, such as adding blank characters onto the end of a string.  Then REPLICATE is the function you'll need.


REPLICATE(string, length)

Open in new window

The string is a VARCHAR, or something implicitly CONVERTable to a VARCHAR.  This means SQL has to know how to turn the string you pass into a VARCHAR, otherwise you'll get an error.  The length is a positive INTEGER telling SQL how many of the character you want returned.
REPLACE
The last function I'll show you is REPLACE.  While this may be the most complex function in this post, it will become one of your most used functions.
REPLACE(searchedString, seekString, replaceWithString)

Open in new window

Basically you first pass the string you're looking through to find the seekString.  If SQL finds the seekString, it will replace that with the replaceWithString.  This is great for more advanced lookups.  Especially when you couple it with the wildcard searches I showed you in a past post.  You can do some pretty impressive things.  The following isn't an example of an awesome - cool replace, but a simple one.

SELECT REPLACE('Luke''s Lightsaber was green in Episode 4.','green','blue')

--this should return:
Luke's Lightsaber was blue in Episode 4.
--someone should call Lucas and remind him of this fact... Special Edition, meh!

Open in new window

Conclusion
These are only 8 of the vast array of SQL Server functions.  In the future I'll show you how to use more of these functions.  Even later, I'll show you how to create your own functions and do even more cool stuff!

If you have any questions, send them in!  I'm always here to help!
2
4,769 Views
Shannon_Lowder
CERTIFIED EXPERT

Comments (15)

CERTIFIED EXPERT
Author of the Year 2009

Commented:
As a counterpoint to the above discussion:  Sometimes bandwidth can also be a consideration.  Using:
    LEFT( someVarcharField, 7 )
...means that only seven characters will need to travel from the server to the application (as opposed to, say, 1,000 -- of which the app will need to discard 993).   Just a thought for the mix.
CERTIFIED EXPERT

Commented:
One from left field -  may not pertain here.

Can you use LEFT$ for SQL as you can in Excel VBA? As a string function it outperforms the variant function LEFT

Cheers

Dave
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
Nah...only 1 "left" function in SQL. The performance isn't terrible so long as you're not setting criteria on it.
To use text manipulation in Excel, the data needs to be brought into Excel first. Once it is there the simplest way to handle it is to use the CopyFromRecordset method. But, then it is already on the sheet and any manipulation will be slow. Manipulating the recordset data before placing it on the spreadsheet would require row by row placement instead of CopyFromRecordset and would be slow as well.

Manipulation in SQL is the prefered way for Excel reporting.
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
@leonstryker: I'm sorry but I disagree.  I don't use use the CopyFromRecordset method you are discussing.  Most of our reports in Excel use a simple DSN that either uses a view or stored procedure and it's trivial to massage the text in the spreadsheet.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community