Difference between revisions of "SUBSTRING(ansi)"

From SQLZOO
Jump to: navigation, search
(Created page with "<table align='right' border='1'> <caption>Compatibility</caption> <tr><th colspan='3'>SUBSTRING(s FROM i FOR j)</th></tr> <tr><td align='center'>'''Engine'''</td><td align='ce...")
 
(One intermediate revision by one user not shown)
Line 3: Line 3:
 
<tr><th colspan='3'>SUBSTRING(s FROM i FOR j)</th></tr>
 
<tr><th colspan='3'>SUBSTRING(s FROM i FOR j)</th></tr>
 
<tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
 
<tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
<tr><td align='left'>ingres</td><td>Yes</td><td>[[SUBSTRING(s,i,j)]]</td></tr>
+
<tr><td align='left'>ingres</td><td>Yes</td><td>[[SUBSTRING |SUBSTRING(s,i,j)]]</td></tr>
<tr><td align='left'>oracle</td><td>No</td><td>[[SUBSTRING(s,i,j)]]</td></tr>
+
<tr><td align='left'>oracle</td><td>No</td><td>[[SUBSTR |SUBSTR(s,i,j)]]</td></tr>
<tr><td align='left'>postgres</td><td>Yes</td><td>[[SUBSTRING(s,i,j)]]</td></tr>
+
<tr><td align='left'>postgres</td><td>Yes</td><td>[[SUBSTRING |SUBSTRING(s,i,j)]]</td></tr>
<tr><td align='left'>sqlserver</td><td>No</td><td>[[SUBSTR(s,i,j)]]</td></tr>
+
<tr><td align='left'>sqlserver</td><td>No</td><td>[[SUBSTRING |SUBSTRING(s,i,j)]]</td></tr>
 
</table>
 
</table>
 
<h1> SUBSTRING (ansi)</h1>
 
<h1> SUBSTRING (ansi)</h1>
Line 19: Line 19:
 
<source lang='sql' class='def e-oracle e-sqlserver'>
 
<source lang='sql' class='def e-oracle e-sqlserver'>
 
SELECT name,
 
SELECT name,
       SUBSTR(name, 2, 5)
+
       SUBSTRING(name, 2, 5)
 
   FROM bbc
 
   FROM bbc
 
</source>
 
</source>
 
<source lang='sql' class='def e-oracle'>
 
<source lang='sql' class='def e-oracle'>
 
SELECT name,
 
SELECT name,
       SUBSTRING(name, 2, 5)
+
       SUBSTR(name, 2, 5)
 
   FROM bbc
 
   FROM bbc
 
</source>
 
</source>

Revision as of 15:26, 16 July 2012

Compatibility
SUBSTRING(s FROM i FOR j)
EngineOKAlternative
ingresYesSUBSTRING(s,i,j)
oracleNoSUBSTR(s,i,j)
postgresYesSUBSTRING(s,i,j)
sqlserverNoSUBSTRING(s,i,j)

SUBSTRING (ansi)

SUBSTRING allows you to extract part of a string.

   SUBSTRING('Hello world' FROM 2 FOR 3) -> 'llo'    

In this example you put the region and the name together for each country.

SELECT name,
       SUBSTRING(name, 2, 5)
  FROM bbc
SELECT name,
       SUBSTR(name, 2, 5)
  FROM bbc
SELECT name,
       SUBSTRING(name FROM 2 FOR 5)
  FROM bbc

See also

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense