18
Learn SQL: Microsoft SQL Server - Episode 11: Extracting data from Strings
In this episode we are going to discuss how to extract characters from a string value.
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.
SSMS provides a built-in function called Left and it takes two arguments/parameters.
We need to provide it with the string value we want to extract the character from, in this case lastName.
The number of characters that we want to return starting from the Left, in this case it is 1.
Left(lastName, 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.
The column that we want to use to extract the value from.
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.
The number of characters we want to extract.
SUBSTRING(lastName, 1, 1)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.
Similar to LEFT their is another function called RIGHT.
Right works like Left in that it takes two arguments.
The string value we want to use to extract characters from.
The number of characters we want to extract starting from the Right.
RIGHT(lastname, 3)This will return the last three characters from the lastName.
This is how we extract values from a string column or just any string value.
Extracting values has really made me hungry...I hope you enjoyed this episode and put it into practice. Signing off...
18