Difference between revisions of "INSTR"
From SQLZOO
(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'...") |
|||
| 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> | ||
| Line 38: | Line 38: | ||
<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> | ||
Revision as of 16:02, 16 July 2012
| INSTR(s1, s2) | ||
|---|---|---|
| Engine | OK | Alternative |
| ingres | No | POSITION(s2 IN s1) |
| mysql | Yes | |
| oracle | Yes | |
| postgres | No | POSITION(s2 IN s1) |
| sqlserver | No | PATINDEX('%'+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 hour from the datetime field whn.
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
