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




 substring command
  • 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.'

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to SQL substring | substring Command in SQL