PATINDEX - Revision history

Connor: Created page with "
Compatibility
PATINDEX('%s1%', s2)

'''Engine'''
2012-07-16T14:26:19Z

Created page with "<table align='right' border='1'> <caption>Compatibility</caption> <tr><th colspan='3'>PATINDEX('%s1%', s2)</th></tr> <tr><td align='center'>'''Engine'''</td><td align='center'..."

New page
<table align='right' border='1'>
<caption>Compatibility</caption>
<tr><th colspan='3'>PATINDEX('%s1%', s2)</th></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 |POSITION(s1 IN s2)]]</td></tr>
<tr><td align='left'>mysql</td><td>No</td><td>[[POSITION |POSITION(s1 IN s2)]]</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>No</td><td>[[POSITION |POSITION(s1 IN s2)]]</td></tr>
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
</table>
<h1>PATINDEX</h1>
<p>PATINDEX('%s1%', 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. The match is case insensitive.</p>
<p></p>
<pre style='width:60ex'>
PATINDEX('%ll%' 'Hello world') -> 3
</pre>

<div class='ht'>
In this example you return the position of the string 'an' within the name of the country.
<source lang='sql' class='def e-sqlserver'>
SELECT name,
PATINDEX('%an%', name)
FROM bbc
ORDER BY name
</source>
<source lang='sql' class='def e-oracle'>
SELECT name,
INSTR(name, 'an')
FROM bbc
</source>
<source lang='sql' class='def'>
SELECT name,
POSITION('an' IN name)
FROM bbc
</source>
</div>

<p>See also</p>
<ul>
<li>[[SUBSTRING |SUBSTRING function]]</li>
<li>[[RIGHT |RIGHT function]]</li>
<li>[[LEFT |LEFT function]]</li>
</ul>