Difference between revisions of "INSTR"

From SQLZOO
Jump to: navigation, search
(Created page with "<table align='right' border='1'> <caption>Compatibility</caption> <tr><th colspan='3'>INSTR(s1, s2)</th></tr> <tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'...")
 
 
(2 intermediate revisions by 2 users not shown)
Line 3: Line 3:
 
<tr><th colspan='3'>INSTR(s1, s2)</th></tr>
 
<tr><th colspan='3'>INSTR(s1, s2)</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>No</td><td>[[POSITION(s2 IN s1)]]</td></tr>
+
<tr><td align='left'>ingres</td><td>No</td><td>[[POSITION |POSITION(s2 IN s1)]]</td></tr>
 
<tr><td align='left'>mysql</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>mysql</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>oracle</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>oracle</td><td>Yes</td><td></td></tr>
<tr><td align='left'>postgres</td><td>No</td><td>[[POSITION(s2 IN s1)]]</td></tr>
+
<tr><td align='left'>postgres</td><td>No</td><td>[[POSITION |POSITION(s2 IN s1)]]</td></tr>
<tr><td align='left'>sqlserver</td><td>No</td><td>[[PATINDEX('%'+s2+'%',s1)]]</td></tr>
+
<tr><td align='left'>sqlserver</td><td>No</td><td>[[PATINDEX |PATINDEX('%'+s2+'%',s1)]]</td></tr>
 
</table>
 
</table>
 +
 
<h1>INSTR</h1>
 
<h1>INSTR</h1>
 
<p>INSTR(s1, s2) returns the character position of the substring s2 within the larger string s1. The first character is in position 1. If s2 does not occur in s1 it returns 0. </p>
 
<p>INSTR(s1, s2) returns the character position of the substring s2 within the larger string s1. The first character is in position 1. If s2 does not occur in s1 it returns 0. </p>
Line 17: Line 18:
  
 
<div class='ht'>
 
<div class='ht'>
In this example you get the hour from the datetime field whn.   
+
In this example you get the position of string 'an' within a country's name.   
 
<source lang='sql' class='def e-postgres e-ingres'>
 
<source lang='sql' class='def e-postgres e-ingres'>
 
SELECT name,
 
SELECT name,
Line 38: Line 39:
 
<p>See also</p>
 
<p>See also</p>
 
<ul>
 
<ul>
   <li>[[SUBSTRING function]]</li>
+
   <li>[[SUBSTRING |SUBSTRING function]]</li>
   <li>[[LEFT function]]</li>
+
   <li>[[LEFT |LEFT function]]</li>
   <li>[[RIGHT function]]</li>
+
   <li>[[RIGHT |RIGHT function]]</li>
 
</ul>
 
</ul>
 +
 +
{{Languages}}

Latest revision as of 20:36, 22 October 2012

Compatibility
INSTR(s1, s2)
EngineOKAlternative
ingresNoPOSITION(s2 IN s1)
mysqlYes
oracleYes
postgresNoPOSITION(s2 IN s1)
sqlserverNoPATINDEX('%'+s2+'%',s1)

INSTR

INSTR(s1, s2) returns the character position of the substring s2 within the larger string s1. The first character is in position 1. If s2 does not occur in s1 it returns 0.

    INSTR('Hello world', 'll') -> 3 

In this example you get the position of string 'an' within a country's name.

SELECT name,
       POSITION('an' IN name)
  FROM bbc
SELECT name,
       PATINDEX('%an%', name)
  FROM bbc
 ORDER BY name
SELECT name,
       INSTR(name, 'an')
  FROM bbc

See also

Language: English  • Deutsch
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense