<

SQL 101 - String Manipulation Functions

Published on
14,188 Points
4,488 Views
2 Endorsements
Last Modified:
Awarded
Community Pick
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
Comment
15 Comments
 
LVL 51

Expert Comment

by:Ted Bouskill
Personally I recommend avoiding using these functions in all my applications unless I have absolutely no other choice and let SQL focus on simply storing, retrieving data with as little manipulation as possible.

The SQL database is a single component in a multi-tier application and therefore usually the busiest.  Usually there are one or more application servers like web servers that are used for data input/output and there are not as busy and there may even be many of them.

String processing can occur in the data access layers (which is usually faster more efficient compiled code), the UI layer (even in client side Javascript on web applications) so why not scale the load out to the other application components and let SQL do what only it can do?
0
 
LVL 60

Expert Comment

by:chapmandew
Well, really it depends.  It makes sense to do it at the db level sometimes if the DB knows the rules for formatting the data...this is especially true for reports.

I do, however, do not suggest this be used in predicates in SQL statements in ways that would invalidate index usage.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
In the case of reports the data will always be manipulated by a UI language.  The DBA is also often a single resource in a company who often doesn't like to make rapid changes to the DB.  So once again why not move the responsibility of tweaking the data outward?

Cheers
Ted
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 60

Expert Comment

by:chapmandew
Always huh?  What about those reports that go directly to Excel?
0
 
LVL 60

Expert Comment

by:chapmandew
Also, these types of functions can be used in other types of statemens to group data.  Maybe you need a list of emails sent by domain name.  Well, this is easily done in SQL with string functions...
0
 
LVL 51

Expert Comment

by:Ted Bouskill
Ah, but in Excel string manipulation is trivial! ;)

That actually proves my point.  I'll guarantee that every example you can provide for displaying data involves a system that has functions that can also manipulate strings far more efficiently.

Even in the email example, I don't store email address as one column.  I split them in the data access layer and normalize the domains so I can apply a domain based index.

Cheers
0
 
LVL 60

Expert Comment

by:chapmandew
Its just as trivial in the database...likely even more so.  :)

I guarantee every example you give for not doing it in the db I can give you one for using it.  These functions are tools...just like anything else.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
I think you completely missed my original point.  If the string formatting occurs in the Excel spreadsheet instead of the SQL server it's less load on the SQL server.

Because the SQL server is a central resource the less cycles spent on tasks that can be moved outward you put on it the better for everyone.

The same with a typical DBA.  Why create a bottleneck by adding tasks for the DBA to do that can be completed by others in the company?

It might be trivial to use string functions in T-SQL but the harm that can occur is not trivial.
0
 
LVL 60

Expert Comment

by:chapmandew
My point isn't to ALWAYS use this type of logic in the db..far from it.  Its just that its there to use when needed.  Not all db functionality is exposed through a UI or even data access layers.  No doubt it can be misused.
0
 
LVL 9

Author Comment

by:Shannon_Lowder
Tedbilly:

I agree that the impact text manipulation can have on a SQL server can be extreme.  I try to caution users to do as much of the formatting in the UI level, or even the business logic layer.  But there are times when I've had to do some text manipulation on the database.

My intent is to teach users how to use these functions, not so much when to use them.  I have written some articles on improving performance of queries and servers.  I do find that on occasion, I can work with developers to move these manipulations to another layer, and that solves it.  But much of the time performance issues come in from the database design, or lack therof, or from the use of the database as it's designed.

If you have the time, I'd appreciate discussing other topics for performance issues in SQL.  I know there are several sources for how to get performance improvements, but many of them require a certain level of ability.  Would you be interested in working on some introductory articles on the subject?
0
 
LVL 50

Expert Comment

by:DanRollins
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.
0
 
LVL 50

Expert Comment

by:Dave
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
0
 
LVL 60

Expert Comment

by:chapmandew
Nah...only 1 "left" function in SQL. The performance isn't terrible so long as you're not setting criteria on it.
0
 
LVL 29

Expert Comment

by:leonstryker
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.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
@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.
0

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month