|
The INSTR string in SQL is used to find the starting location of a pattern in a string. This function is available in MySQL and Oracle, though they have slightly different syntaxes:
The syntax for the Length function is as follows:
MySQL: INSTR (str, pattern): Find the staring location of pattern in string str.
Oracle: INSTR (str, pattern, [starting position, [nth location]]): Find the staring location of the nth occurrence of pattern beginning in the starting position-th position in string str.
Let's take a look at some examples. Assume we have the following table:
Table Geography
| region_name | store_name |
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
Example 1 (both Oracle and MySQL):
SELECT INSTR(store_name,'o')
FROM Geography
WHERE store_name = 'Los Angeles';
Result:
3
Example 2 (both Oracle and MySQL):
SELECT INSTR(store_name,'p')
FROM Geography
WHERE store_name = 'Los Angeles';
Result:
0
In this case, the pattern p does not exist in string 'Los Angeles', so the function returns 0.
Example 3 (Oracle only):
SELECT INSTR(store_name,'e', 1, 2)
FROM Geography
WHERE store_name = 'Los Angeles';
Result:
10
The second occurrence of e is in the 10th position.
SQL TRIM >>
Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.
Copyright © 2012 1keydata.com All Rights Reserved.
Privacy Policy
|