Table of Contents
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:
- SQL Substring Function
- SQL Left Function
- SQL Right Function
SQL String is sequence of characters or bytes, enclosed within either two single quotes (
' ') or two double quotes (
- SQL strings are enclosed in single or double quotation marks.
- In place of column name, we can place sequence of characters as a string in SELECT Statement or in Substring function also.
- 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.
String or Expression Datatype Return Type
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
string_expressionas 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_id studentname admissionno admissiondate enrollmentno date_of_birth city class_id
101 reema 10001 02-02-2000 e15200002 02-02-1990 email@example.com surat 2
102 kriya 10002 04-05-2001 e16200003 04-08-1991 firstname.lastname@example.org surat 1
103 meena 10003 06-05-1999 e15200004 02-09-1989 email@example.com vadodara 3
104 carlin 2001 04-01-1998 e14200001 04-04-1989 firstname.lastname@example.org vapi 1
105 dhiren 2002 02-02-1997 e13400002 02-02-1987 email@example.com vapi 2
106 hiren 2003 01-01-1997 e13400001 03-03-1887 firstname.lastname@example.org surat 2
107 mahir 10004 06-09-2000 e15200003 07-09-1990 email@example.com vapi 3
108 nishi 2004 02-04-2001 e16200001 03-02-1991 firstname.lastname@example.org vadodara 1
result_id student_id examname examdate subject obtainmark totalmarks percentage grade status
3001 101 sem1 07-08-2001 1 80 100 80 A+ pass
3002 101 sem1 08-08-2001 2 76 100 76 A+ pass
3003 102 sem3 05-05-2000 3 67 100 67 A pass
3004 102 sem3 06-05-2000 4 89 100 89 A+ pass
3005 102 sem3 07-05-2000 5 90 100 90 A+ pass
3006 103 sem5 08-09-1998 6 55 100 55 B pass
3007 103 sem5 09-09-1998 7 30 100 30 D fail
3008 103 sem5 10-09-1998 8 34 100 34 D fail
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.
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
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
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.
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')
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.
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)
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
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;
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.