Monday, November 5, 2007

SQL SUBSTRING

The Substring function in SQL is used to grab a portion of the stored data. This function is called differently for the different databases:

  • MySQL: SUBSTR(), SUBSTRING()
  • Oracle: SUBSTR()
  • SQL Server: SUBSTRING()
The most frequent uses are as follows (we will use SUBSTR() here):

SUBSTR(str,pos): Select all characters from starting with position . Note that this syntax is not supported in SQL Server.

SUBSTR(str,pos,len): Starting with the th character in string and select the next characters.

Assume we have the following table:

Table Geography

region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

Example 1:

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

Result:

's Angeles'

Example 2:

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

Result:

'an D'

No comments: