SQL substring | substring Command in SQL - sql - sql tutorial - learn sql

The Substring function in SQL is used to return a portion of the string. Each database provides its own way(s) of doing this:

MySQL: SUBSTR( ), SUBSTRING( )
Oracle: SUBSTR( )
SQL Server: SUBSTRING( )

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Syntax

The syntax for SUBSTRING is as follows (we will use SUBSTR( ) here):

SUBSTR (str, position, [length])

where position and length are both integers.
This syntax means the following: Start with the position-th character in string str, select the next length characters.
In MySQL and Oracle, length is an optional argument.
When length is not specified, the entire string starting from the position-th character is returned.
In SQL Server, length is required.

SUBSTR() can be used in SELECT, WHERE, and ORDER BY clauses.

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

We use the following table for our examples.

Table Geography

Region_Name	Store_Name
East	Chicago
East	New York
West	Los Angeles
West	San Diego

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example 1

SELECT SUBSTR (Store_Name, 3) 
FROM Geography 
WHERE Store_Name = 'Los Angeles';

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Result:

SUBSTR (Store_Name, 3)
s Angeles

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example 2

SELECT SUBSTR (Store_Name, 2, 4) 
FROM Geography 
WHERE Store_Name = 'San Diego';

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Result:

SUBSTR (Store_Name, 2, 4)
an D

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example 3

SELECT Store_Name 
FROM Geography 
ORDER BY SUBSTR (Store_Name, 2, 4);

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Result:

Store_Name
San Diego
New York
Chicago
Los Angeles

In Example 3, the ORDER BY criteria is based on the 2nd to 5th characters in the string. Below are the 2nd to 5th characters for each Store_Name:

Store_Name	SUBSTR(Store_Name,2,4)
Chicago	hica
New York	ew Y
Los Angeles	os A
San Diego	an D

Based on the above, we can see that 'San Diego' would be first, followed by 'New York,' 'Chicago,' and finally 'Los Angeles.'

Related Searches to SQL substring | substring Command in SQL

sql substring leftsql substring rightsql charindexinstr in sqlsql string lengthsubstring in mysqlsplit in sqlsql midsubstring en sqlsql substring syntaxhow to substring in sqlsubstring in sql examplesubstring in sql querysubstring in sql oraclesql server substring leftsubstring sql examplesubstring_index sqlsql server substring rightsql substring indexsubstring function sqlaccess sql substringsql substring examplesql server substring functionselect substring sqlsql query substringsql substring leftsql substring rightsql substring functionsubstring command in sql