Difference between revisions of "SUBSTRING(ansi)"

From SQLZOO
Jump to: navigation, search
(3 intermediate revisions by 2 users 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>[[SUBSTR(s,i,j)]]</td></tr>
+
<tr><td align='left'>mysql</td><td>Yes</td><td></td></tr>
<tr><td align='left'>postgres</td><td>Yes</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'>sqlserver</td><td>No</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>[[SUBSTRING |SUBSTRING(s,i,j)]]</td></tr>
 
</table>
 
</table>
 +
 
<h1> SUBSTRING (ansi)</h1>
 
<h1> SUBSTRING (ansi)</h1>
 
<p>SUBSTRING allows you to extract part of a string. </p>
 
<p>SUBSTRING allows you to extract part of a string. </p>
Line 16: Line 18:
  
 
<div class='ht'>
 
<div class='ht'>
In this example you put the region and the name together for each country.
+
In this example you get the 2nd to 5th character from each country's name.  
 
<source lang='sql' class='def e-oracle e-sqlserver'>
 
<source lang='sql' class='def e-oracle e-sqlserver'>
 
SELECT name,
 
SELECT name,
Line 39: Line 41:
 
   <li>[[NVL |NVL function]]</li>
 
   <li>[[NVL |NVL function]]</li>
 
</ul>
 
</ul>
 +
 +
{{Languages}}

Revision as of 12:02, 28 October 2012

Compatibility
SUBSTRING(s FROM i FOR j)
EngineOKAlternative
ingresYesSUBSTRING(s,i,j)
mysqlYes
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 get the 2nd to 5th character from each country's name.

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

Language: English  • Deutsch
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense