SQL 201 – String Manipulation Functions

In a previous post I introduced you to calculated fields, and mentioned there were many other functions you could use to compute values or columns.  It’s time to introduce you to the eight most used functions when it comes to manipulating strings in SQL

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)

The string can be a variable containing a VARCHAR or NVARCHAR, a literal VARCHAR or NVARCHAR, or a column with a type of VARCHAR or NVARCHAR.  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, positive INTEGERS only please!

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.  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)

LTRIM and RTRIM

Whenever you deal with data that has come from free form fields, users will invariably add crap 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)

You pass it a string (CHAR, NCHAR, VARCHAR, NVARCHAR), and it takes off either the leading spaces, or the trailing spaces.

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)

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)

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.

Conclusion

These are only 8 of the functions SQL understands.  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!

Previous: Calculated Fields Next: Date Manipulation Functions

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

1 comment

  1. Good info and I’ve often used the 3rd parameter when looping through strings doing some sort of string separation routines. It’s very handy one of my most-often-used functions.

Leave a comment

Your email address will not be published. Required fields are marked *