How to Extract a Substring From a String in T-SQL Database: MS SQL Server Operators: SUBSTRING CHARINDEX LEN Table of Contents Problem: Example 1: Solution 1: Discussion: Example 2: Solution 2: Discussion: Example 3: Solution 3: Discussion: Problem: You would like to extract substrings from a text column in SQL Server. Example 1: In the emails table, there is an email column. You'd like to display the first seven characters of each email. The table looks like this: email jake99@gmail.com tamarablack@zoho.com notine@yahoo.fr jessica1995@onet.pl Solution 1: SELECT email, SUBSTRING(email, 1, 7) AS substring FROM emails; The result is: emailsubstring jake99@gmail.comjake99@ tamarablack@zoho.comtamarab notine@yahoo.frnotine@ jessica1995@onet.pljessica Discussion: Use the SUBSTRING() function. The first argument is the string or the column name. The second argument is the index of the character at which the substring should begin. The third argument is the length of the substring. Watch out! Unlike in some other programming languages, in T-SQL the indexes start at 1, not 0. This means the first character has index 1, the second character has index 2, etc. SUBSTRING(email, 1, 7) will return the substrings of the values in the email column that start at the first character and go for seven characters. Example 2: You'd like to display the substring between indexes 2 and 6 (inclusive). Solution 2: SELECT email, SUBSTRING(email, 2, 5) AS substring FROM emails; The result is: emailsubstring jake99@gmail.comake99 tamarablack@zoho.comamara notine@yahoo.frotine jessica1995@onet.plessic Discussion: You use the SUBSTRING() function just as in the previous examples. This time, the second argument of the function is 2, since we want to start at index 2. The length of the substring is 5 (end_index - start_index + 1). Example 3: You'd like to display the substring that starts at the @ sign and ends at the end of the string, but you don't know the exact indexes or lengths. Solution 3: SELECT email, SUBSTRING(email, CHARINDEX('@', email), LEN(email) - CHARINDEX('@', email) + 1) AS substring FROM emails; The result is: emailsubstring jake99@gmail.com@gmail.com tamarablack@zoho.com@zoho.com notine@yahoo.fr@yahoo.fr jessica1995@onet.pl@onet.pl Discussion: You use the SUBSTRING() function just as in the previous examples. This time, you're looking for a specific character whose position can vary from row to row. To find the index of the specific character, you can use the CHARINDEX(character, column) function where character is the specific character at which you'd like to start the substring (here, @). The argument column is the column from which you'd like to retrieve the substring; it can also be a literal string. The third argument of the SUBSTRING() function is the length of the substring. You can calculate it using the CHARINDEX() and the LEN() functions. You do this by subtracting the index from the column length then adding 1: LEN(email) - CHARINDEX('@', email) + 1 You may also want to retrieve a substring that doesn't end at the end of the string but at some specific character, e.g., before .. Here's how you can do this: SELECT email, SUBSTRING(email, CHARINDEX('@', email), CHARINDEX('.', email) - CHARINDEX('@', email)) AS substring FROM emails; The result of this query is: emailsubstring jake99@gmail.com@gmail tamarablack@zoho.com@zoho notine@yahoo.fr@yahoo jessica1995@onet.pl@onet The part CHARINDEX('.', email) - CHARINDEX('@', email) simply calculates the length of the substring. Recommended courses: SQL Basics in SQL Server Common Functions in SQL Server Recommended articles: SQL Server Cheat Sheet How to Learn T-SQL Querying 14 Differences Between Standard SQL and Transact-SQL How to Recognize SQL Text Data Type Microsoft SQL Server Pros and Cons SQL String Functions: A Complete Overview See also: How to Replace Part of a String in T-SQL How to Concatenate Strings in SQL How to Change Text to Lowercase in SQL How to Convert a String to Uppercase in SQL How to Concatenate String and NULL Values in SQL Server How to Replace Part of a String in SQL Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query