SQL SUBSTRING Function Explained [Beginners]

Related Searches: sql substring, sql server substring, sql select string, substring syntax in sql, sql query substring, substring mssql example, sql select only part of a string, sql server mid function, extract part of string sql, sql select where part of string, sql server cut string, sql substring from character, substring format, sql get string, sql truncate string, sql find substring, sql left string, sql substring index, select first 2 characters sql

 

SQL Truncate String

SQL provides some effective functions to manipulate string values specially when we are working with database systems probably each table have VARCHAR or NVARCHAR or TEXT datatype column or columns . The number of characters contain in that string is sometimes too long.

Truncating string means to manipulating or to do extraction from string. SQL mainly provides three truncate functions which are working with string:

1. SQL Substring Function
2. SQL Left Function
3. SQL Right Function

 

SQL String

SQL String is sequence of characters or bytes, enclosed within either two single quotes (' ') or two double quotes (" ").

1. SQL strings are enclosed in single or double quotation marks.
2. In place of column name, we can place sequence of characters as a string in SELECT Statement or in Substring function also.
3. Special characters (e.g. single or double quotes) in the literal string need to be escaped.

 

SQL Substring Function | SQL Server Substring

When we are working with SQL Queries, we are performing different operations on string data like search, replace, extraction, character calculation, analysis.

SQL Substring function is used to extract part of string from column value data, it returns a portion of string as per the argument value given in the function. This function is widely used by developers in SQL Queries for manipulating string values.

SQL Server provides various built-in functions to do various operation and calculation. Generally, these functions are used with SQL Select Query to do calculation on numeric data value and do various operation in string values.

Substring() in SQL server is a function which Extract a portion of the string. Substring function is one of the built-in functions of SQL and that helps to get a set of character data of the text value in the queries. This function is widely used by database developers in the queries.

 

SQL Substring Syntax

Substring() extracts a string with a specified length, starting from a given location in the Expression string.

Substring(String_Expression | column_name, start_position, length)

• String_Expression | column_name: it is set of characters, binary, text or image. Expression is the value of string from which function will extract the portion as per other argument values.
• start_position: It is a index value of Starting Position from which extraction of string will begin 1). If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.
• length: It is a positive integer or bigint value which defines number of characters to be extracted from string. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

 

Return value of Substring Function

SQL substring Function will return resulting string in character data type if argument expression is in char, varchar or text type and if function argument string is Binary datatype than resulting string will also be of Binary data as shown in below table.

 

SELECT Query with Substring

SQL Substring function can be implemented with the SQL SELECT Query to retrieves data any manipulating or retrieving portion of data value from the tables.

Substring Function in SQL SELECT Query format

SELECT column_name | Substring(string_expression|column_name,start_position,length) | *,
column_name| substring(string_expression|column_name,start_position,length) | *,..
From table_name [WHERE condition][GROUP BY column,..] [ORDER BY Column,..];

• We can give column_name as a string_expression as an argument of substring function, the value of that particular column will be considered for string extraction.
• Substring function can be applied on more than one column of a table in select query, each function is mentioned after SELECT clause and separated by comma.

 

MySQL Substring Example

Let's consider two Tables of a Relation Database As examples
Student Table

Result Table

 

SQL Substring Query to Extract Part of string

Using SQL Substring function extract first 4 letters of employee name

SELECT Substring(Emp_name, 1, 3) AS Name, city
FROM tblemp;

In Above Query we used SQL Substring function on employee name column to extract starting characters of name.

OUTPUT:

Use SQL Substring function to get first two letters of city name with starting three characters of department location of each employee by joining two tables employee and department with LEFT INNER JOIN

SELECT Substring(tblemp.city, 1, 2) AS 'Employee City', Substring(tbldept.Dept_location, 1, 3) AS 'Depatment location'
FROM tblemp INNER JOIN tbldept ON tblemp.Dept_id = tbldept.Dept_id

OUTPUT:

 

SQL MID() Function

Like SQL Substring function, SQL SERVER Mid function is also used to extract portion of text or group of characters from String column value or from String Expression.

SELECT MID(column_name|string_expression, start_position, length) FROM table_name;

 

Difference between MID() and Substring()

The main difference between MID() and Substring() is MID function start indexes the character from 1 whereas Substring function starts indexes the character from 0.

Use SQL MID() to retrieve part of department id, note that department id is a int datatype value so to apply MID function we need to convert it  into varchar data type ,

SELECT MID(CAST(Dept_id AS varchar(10)), 1, 2) AS 'Department number' , Dept_name
FROM tbldept

OUTPUT:

Now we are taking an example which shows difference between substring and MID function

 

To display employee details whose email domain name is ended in '.in'  using SQL Substring Function

SELECT Emp_id, Emp_name, street, city, Email, Emp_contact, Salary, Dept_id
FROM  tblemp
WHERE (SUBSTRING(Email, LEN(Email) - 2, 3) = '.in')

 

To display employee details whose email domain name is ended in '.in'  using SQL MID Function

SELECT Emp_id, Emp_name, street, city, Email, Emp_contact, Salary, Dept_id
FROM tblemp
WHERE (MID(Email, LEN(Email) - 3, 3) = '.in')

Above both query are the example of  comparison between substring and MID function, to extract last 3 characters of a string with substring function using LEN function we need to subtract 2 from length of string where as with MID function we need to subtract 3 for extraction as it start index from 0.

OUTPUT:

 

SQL Substring Function | SQL Select String with Where Condition

Execute a SQL Query to see the employee details who are working in Department ‘Sales’ with last two letters of city name and first four letters of department name

SELECT tblemp.Emp_name, Substring(tblemp.city, LEN(tblemp.city) - 2, 3) AS City, tblemp.Emp_contact, tbldept.Dept_name, Substring(tbldept.Dept_location, 1, 4) AS 'Deptment Name'
FROM tblemp INNER JOIN
 tbldept ON tblemp.Dept_id = tbldept.Dept_id
WHERE (tbldept.Dept_name = 'Sales')

OUTPUT:

Taking one more example of SQL Substring function with SELECT WHERE Clause where Substring function is used in where condition

To retrieve employee details whose domain name of email is ended with ‘.com’

SELECT Emp_id, Emp_name, street, city, Email, Emp_contact, Salary, Dept_id
FROM tblemp
WHERE  (Substring(Email, LEN(Email) - 2, 3) = 'com')

In above query we have used SQL Substring function in WHERE clause to frame conditional selection of records from table employee.

OUTPUT:

 

SQL Substring Function | SQL Substring from character

There is a situation where we need to extract a portion of string based on a specific character within that string or in expression, in such scenario we need to use CHARINDEX function with SQL Substring Function

In SQL Substring based on or from character the argument of Substring function starting position will be the index of specified character within a string

For example, here I am adding one column in employee table

SELECT Email, Substring(Email, CHARINDEX('@', Email) + 1, LEN(Email) - CHARINDEX('@', Email)) AS domain
FROM tblemp
ORDER BY Email;

In the above Query, to extract the domain name from email id, first we have used the CHARINDEX function to search the ‘@’ character in the email. Then we have added 1 in the value if CHARINDEX (‘@’) as a starting position for Substring function.

• The start position of the domain name will be CHARINDEX ('@', email) + 1
• The length of the domain: LEN (email)-CHARINDEX('@', email)

OUTPUT:

 

SQL LEFT Function

SQL LEFT Function extract left part of given string value of expression with specified number of characters

 

SQL LEFT Function Syntax

LEFT ( Column_name | String_expression , length )

Column_name | String_expression: is the string value of VARCHAR, TEXT OR NVARCHAR type from which LEFT function is extracting characters.

Length: Number of characters to be extracted.

 

SQL LEFT Function Examples with column name

Extract first 4 initial characters of email and first four numbers of contact details of each employee

SELECT Emp_name, LEFT(Email, 4) AS 'Email initials', LEFT(Emp_contact, 4) AS Contact, Salary
FROM tblemp

OUTPUT:

'

 

SQL SUBSTRING_INDEX () Function

SQL SUBSTRING_INDEX() returns a substring from a string before a specified number of occurrences of the delimiter.

 

SQL SUBSTRING_INDEX () Function Syntax

SUBSTRING_INDEX(String_Expression| Column_name ,delimiter,Count)

• String_Expression| Column_name : The original string from which String will be extracted
• delimiter : it is a character that acts as a delimiter. The function performs a case-sensitive match when searching for the delimiter.
• count :It identifies the number of times to search for the delimiter. It can be both a positive or negative number. If it is a positive number, this function returns all to the left of the delimiter. If it is a negative number, this function returns all to the right of the delimiter.

SQL SUBSTRING_INDEX () Function Returns portion of a given string till the first occurrence of given delimiter value.

 

SQL SUBSTRING_INDEX Example

Use SUBSTRING_INDEX() function to extract value of email id to the first occurrence of '@' delimiter

SELECT SUBSTRING_INDEX(Email, [@], 1) AS Sub_String
FROM  tblemp;

OUTPUT:

 

Summary

In Above Article on SQL Substring Function Gives use of Truncate SQL Functions, SQL String, SQL Server Substring, Syntax and Examples of SQL Substring function, also covers SQL Character finding and Searching examples of Substring. in last part we have covered SQL Left Function with example. SQL Substring and SQL MID Functions are used to Extract portion or part of string given as an expression or in column name within function argument.

 

Further Reading

https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html