Extract string before a special character

Here is an easy way to get (extract) all of the characters before a special character in a string.  In our example we have a listing of email addresses and we want to get the name before the @domain.

Somebody from the network team needs to get all of the usernames for the network and those just happen to be equal to their email address.  We can help them out by extracting just the name from the email address.

SELECT EmailAddress
FROM [dbo].[DimEmployee]

Using the SUBSTRING and CHARINDEX functions, this is accomplished easily.  We are lucky since the @ sign is unique enough to be our “special character” to search for.

Below are some links to read more about these functions.

https://docs.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql

The SUBSTRING function returns part of a string and the CHARINDEX function “Searches an expression for another expression and returns its starting position if found.”

Using the charindex function allows you to search for the @ sign and find its starting position and then the substring is used to extract the characters before the @ sign.  That’s a mouthful, but the query below shows how easy it is.

SELECT EmailAddress
 ,SUBSTRING(emailaddress, 0, charindex('@', emailaddress, 0))
FROM [dbo].[DimEmployee]

There are many powerful built in functions in SQL Server and you do not have to always create your own or create some sort of procedural code to get the results you need.

It really is worth your time to look through the available functions to see what is a available.

https://docs.microsoft.com/en-us/sql/t-sql/functions/functions