Separating text - SQL Tutorial

- [Lecturer] In addition to joining or concatenating strings of text we can do the reverse of this which is to truncate or shorten a string of text. Now, we can achieve shortening or truncating a string of text by the use of understanding how text is stored in a text field. One important concept to understand is that text have positions in a stored field. So for instance, our first name field that has Frank the letter F sits in position number one and R two, A three, and so forth. Now this positioning matters when it comes to manipulating strings of text. Now, let's say our management at WSDA Music is really happy about our mailing list, however, they want to actually have a little more precise postal codes. If you notice, the current postal codes all have a dash and some numbers following and these numbers are usually referred to as the zip+4 code. And this isn't necessary for mail to find their way to the intended recipient. So let's go ahead and remove this zip+4 code and let's start our removal of this by employing one new string function. So first, let's take a look at the length of this postal code. And to do this, we're going to employ the length function. I'm going to say open parenthesis and include the field that we want to find out the length of. And in this case, it's the postal code. Now I'm just going to hit run and we have a brand new column. And what this column displays is the length of our postal codes. Now, if we take a look at these lengths, they all vary and that is because they don't always have the equal number of digits. If we take a look at row number five, the zip+4 code is actually three digits, whereas in other cases it's four digits. So we can actually start our removal of parts of the zip code by employing the sub string function. I'm going to go into a new line here and start typing, S, U, B, S, T, R and open parenthesis. And this sub string function actually removes the extra US postal codes from this particular field. To do this, we're going to say first, the field that we want to manipulate or change which is the postal code. Then we specify the first digit that we want to start the removal, which is from digit number one or position number one. And then we want to keep the five postal code digits that will allow our mailing address or letters. We want to keep the five digits or the five zip codes that are going to allow our packages or postcards to get to their intended recipients. So let's indicate that we want to keep five digits by putting a comma in five and then we're going to close our sub string function and we can also alias this as five digit postal code. Okay, now let's go ahead and run our updated statement and check out the result. Here we now have a new postal code and this time it's nicely formatted to only the first five digits, which as we can verify here is exactly what we wanted to do. Now we could respond to WSDA Music Management and say here is the updated mailing list with a nicely updated and cleaner five digit postal code.