Learn SQL: Microsoft SQL Server - Episode 11: Extracting data from Strings

Let's say for instance, we wanted to pull data from the Person.Person table. As we should know by now, this table contains all the information about all people in the database, customers and employees.

In this example, we will be selecting only the firstName and the lastName columns from the Person.Person table. However, we do not want to pull the entire lastName of person in the database, instead we just want to pull the first character from the lastName value.

LEFT

SSMS provides a built-in function called Left and it takes two arguments/parameters.

First Value

We need to provide it with the string value we want to extract the character from, in this case lastName.

Second Value

The number of characters that we want to return starting from the Left, in this case it is 1.

We can test this in SSMS, and to make it easier to understand we can give the Left function an Alias of Last Initial.

We can change the number of characters that get returned by modifying the second argument.

Another way to extract those characters is by using a Substring.

SubString is another built-in function that takes three arguments/values.

SUBSTRING

First Value

The column that we want to use to extract the value from.

Second Value

Here we need to provide the position we want to start extracting the value at, eg choosing 2 would mean we want to start extracting from the second character, starting from the Left.

Third Value

The number of characters we want to extract.

As we can see, it works similar to LEFT, we could also assign an Alias to the SUBSTRING function to make things easier to understand.

RIGHT

Similar to LEFT their is another function called RIGHT.

Right works like Left in that it takes two arguments.

First Value

The string value we want to use to extract characters from.

Second Value

The number of characters we want to extract starting from the Right.

This will return the last three characters from the lastName.

Extracting values has really made me hungry...I hope you enjoyed this episode and put it into practice. Signing off...