Copyright

SQL TRUNCATE String: Tutorial & Overview

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: SQL TRUNCATE Table: Tutorial & Explanation

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:00 Truncating a String
  • 0:56 The LEFT Function
  • 1:41 The RIGHT Function
  • 2:22 The SUBSTR Function
  • 4:37 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed
Lesson Transcript
Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

SQL offers some nifty commands to manipulate and work with strings. In this lesson, we'll discuss three commands, each with their own uses, that can reduce strings to an appropriate size.

Truncating a String

Even strings in databases can be long-winded. Sometimes they contain extra information that we don't necessarily need for database queries or updates. We need tools to help us shorten the length of strings in order to work with them.

SQL provides three nifty commands that help us manipulate and work with strings: LEFT, RIGHT, and SUBSTR.

There are times when you might want to insert a value into a string field, but limit the size of the string since the field is set to a specific length. This can be accomplished with the LEFT command.

Perhaps there are strings that might have leading spaces or characters that you don't want in the final input. In this case, we use the RIGHT command.

There is also a third option, called SUBSTR, which involves extracting a section of a string, neither the right-most or left-most pieces, but a chunk of the string somewhere in between.

The LEFT function

Let's say we have a string field in a database that is limited to 255 characters. For example, 'artistName' stores the band name in a music database.

In this case, we'd use the command LEFT(string, length), where string is the field or text being worked with and length is the number of characters to insert.

This example inserts the artist name into the field and the Form prefix is only used to indicate that it could have come from a user web form:


INSERT INTO tblArtist (artistName)
VALUES (LEF(Form.artist_name, 255);


The LEFT command can also be used as part of the SELECT statement. Let's say you do have 5,000+ characters in the albumNotes field, but only want to display the user with the first 50 characters. To this end, we would write this line:


SELECT LEFT(albumNotes, 50);


The RIGHT Function

The RIGHT function displays characters of a string starting from the right-most character.

The syntax for this function is RIGHT(string, length), where string is the field or string you are truncating, and length indicates how much of the string you are extracting.

In this example, all the data entered into the 'artistCountry' field ends with the country code. Albania would be stored as ALBANIA-AL, and Denmark as DENMARK-DK. If we would like only the suffix, the RIGHT command can accomplish this like this:


SELECT RIGHT(countryName, 3) FROM tblArtist WHERE countryName = 'ALBANIA-AL';


The value is extracted from the field starting at position 3 (remember strings start counting at character 0!), returning AL.

The SUBSTR Function

What if the country code is stored with both a prefix and a suffix? For example, Denmark is stored as 98-DENMARK-DK, but we only want to return Denmark. This is possible by using a SUBSTR() with a combination of LEFT() and RIGHT(). But first, let's look at the SUBSTR() command to get a sense of how it works.

The basic syntax is SUBSTR(string, position, length), where position and length are numbers. For example, start at position 1 in the string countryName, and select 15 characters. Length is optional in MySQL and Oracle, but required in SQL Server.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support