Difference between revisions of "POSITION"

From SQLZOO
Jump to: navigation, search
(Created page with "<table align='right' border='1'> <caption>Compatibility</caption> <tr><th colspan='3'>POSITION(s1 IN s2)</th></tr> <tr><td align='center'>'''Engine'''</td><td align='center'>'...")
 
Line 7: Line 7:
 
<tr><td align='left'>oracle</td><td>No</td><td>[[INSTR |INSTR(s2,s1)]]</td></tr>
 
<tr><td align='left'>oracle</td><td>No</td><td>[[INSTR |INSTR(s2,s1)]]</td></tr>
 
<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr>
<tr><td align='left'>sqlserver</td><td>No</td><td>[[PATINDEX('%'+s1+'%',s2)]]</td></tr>
+
<tr><td align='left'>sqlserver</td><td>No</td><td>[[PATINDEX |PATINDEX('%'+s1+'%',s2)]]</td></tr>
 
</table>
 
</table>
 
<h1>POSITION</h1>
 
<h1>POSITION</h1>
Line 38: Line 38:
 
<p>See also</p>
 
<p>See also</p>
 
<ul>
 
<ul>
   <li>[[SUBSTRING function]]</li>
+
   <li>[[SUBSTRING |SUBSTRING function]]</li>
   <li>[[RIGHT function]]</li>
+
   <li>[[RIGHT |RIGHT function]]</li>
 
   <li>[[LEFT |LEFT function]]</li>
 
   <li>[[LEFT |LEFT function]]</li>
 
</ul>
 
</ul>

Revision as of 16:18, 16 July 2012

Compatibility
POSITION(s1 IN s2)
EngineOKAlternative
ingresYes
mysqlYes
oracleNoINSTR(s2,s1)
postgresYes
sqlserverNoPATINDEX('%'+s1+'%',s2)

POSITION

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

   POSITION('ll' IN 'Hello world') -> 3      

In this example you return the position of the string 'an' within the name of the country.

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

See also

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense