Strings

1. Strings

This lesson will be a gentle introduction to working with text values, or strings, in SQL Server.

2. LEN

We can find the length of a text column (which means the number of characters, including spaces) using the LEN function. In this example, we first select the description column, and then the length of the description column, with a column alias. It's useful to know the total length of a string, as a starting point for use in other string calculations.

3. LEFT

If we want to extract a number of characters from the beginning of a string, use the LEFT function. The function syntax is LEFT,open parentheses, the column name, a comma, the number of characters to extract, then the closing parentheses. In this example, we select the description column, and then the first 20 characters from the description column, aliased as first_20_left.

4. RIGHT

The RIGHT function starts from the right hand side of the string and works back to extract the number of characters we specify. In this example, we again SELECT the description column, then extract the last 20 characters from the right hand side of the string.

5. CHARINDEX

The CHARINDEX function helps us find a specific character within a string. In this example, we're going to find the first underscore within the url column, from the courses table. The syntax is SELECT CHARINDEX,open parenthesis, then a single quote, then the character we want to find, in this case the underscore, a closing single quote, the name of the column we want to find the character within, then the closing parenthesis. Of course, we provide a column alias, and, for comparison purposes, we retrieve the url column so we can check that it works as expected.

6. SUBSTRING

Sometimes we need to extract from the middle portion of a string, as opposed to from the left or right edges. That's a job for SUBSTRING. The syntax is SELECT SUBSTRING, open parenthesis, the column name, the number of the character to start from, then the number of characters to extract, then the closing parenthesis. Here we extract the string "datacamp-dot-com", which,as we can see, begins twelve characters in from the left hand edge.

7. REPLACE

Finding and replacing text is a common task, so let's see how we can do this in T-SQL. We've seen how to find, using CHARINDEX, but, we don't need to use it for this task. Instead, the REPLACE function does the hard work for us. In this example, we replace all underscores in the url column with hyphens. We don't need to specify the positions of the character, or even that there are more than one - REPLACE does the job for us and all instances of an underscore are replaced.

8. Let's practice!

Although we may spend most of our time dealing with numeric data, we have now learned a few valuable tools to help us when working with strings. Let's try them out!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.