Learn SQL: Microsoft SQL Server - Episode 11: Extracting data from Strings
Tags: beginners, sql, database, programming
Published: 2021-11-12 06:33:10

**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, <u>customers and employees</u>.

![skid-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gyl2xdfk6a3hn5irtu73.gif)

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.

![skid-cover-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzjnsvq6m255dyodynlc.png)

###LEFT

<abbr title="SQL Server Management Studio">SSMS</abbr> provides a ``built-in`` function called **Left** and it takes two <u>arguments/parameters</u>.

####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``.

```
Left(lastName, 1)
```

![skid-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gyl2xdfk6a3hn5irtu73.gif)

We can test this in <abbr title="SQL Server Management Studio">SSMS</abbr>, and to make it easier to understand we can give the **Left** function an **Alias** of ``Last Initial``.

![discuss-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/03cv0bmmbi50kkw8gq8b.gif)

>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 <u>arguments/values</u>.

![skid-cover-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzjnsvq6m255dyodynlc.png)

###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.

```
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.

![discuss-3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/smq2myox6r9onotpcv6l.gif)

![skid-cover-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzjnsvq6m255dyodynlc.png)

###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**.

```
RIGHT(lastname, 3)
```

This will return the last three characters from the **lastName**.

![discuss-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ed03zd3v9uq5f6qh9hjs.gif)

**This is how we extract values from a string column or just any string value.**

![skid-cover-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s7u6h3c7516254hcdpg4.png)

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

![friday-sleepday](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lvm2ew4gmx2gwtxzvxo5.gif)