|
Custom Search
| |
|
MySQL LOCATE() function with examples LOCATE() function finds the location of the first occurrence of the substring in a given string. It returns an integer number counting from left to right in the given string. If the substring is not found within the given string, LOCATE() function returns 0. POSITION() function is a synonyms of LOCATE() function but has a subtle difference in the format of arguments passed into it. Syntax LOCATE(substr, str, pos) Parameter Values
LOCATE() function returns 0 if substr is not in str. Returns NULL if substr or str is NULL. Examples 1. The query below finds the position of the first occurrence of the space character " " within the given string "Sales Representative". It returns integer number 6 which counts from left to right in the given string. -- Result: 6 select locate(" ", "Sales Representative"); 2. The query below shows that there are two space characters in the given string "Sales Representative Office". It returns integer number 6 which is the position of the first occurrence counting from left to right in the given string. -- Result: 6 select locate(" ", "Sales Representative Office"); 3. The query below finds the position of the first occurrence of the string "xyz" within the given string "Sales Representative Office". It returns integer number 0 as the substring does not exist in the given string. -- Result: 0 select locate("xyz", "Sales Representative Office"); 4. In the query below, we want to extract substring "Sales" which is the string before the first occurrence of the space character " ". LOCATE function here is used to get the position of the first space character, then SUBSTR function is used to extract substring "Sales" from string "Sales Representative Office". -- Result: Sales select substr("Sales Representative Office", 1, locate(" ", "Sales Representative Office")-1); 5. In the query below, we want to extract substring "Representative Office" which is the string after the first occurrence of the space character " ". LOCATE function here is used to get the position of the first space character, then SUBSTR function is used to extract substring "Representative Office" from "Sales Representative Office". -- Result: Representative Office select substr("Sales Representative Office", locate(" ", "Sales Representative Office")+1); 6. In the query below, from suppliers table, we extracted partial phone numbers before the first occurrence of the dash character "-". -- Result: 39 records returned select substr(phone, 1, locate("-", phone)-1) as "Phone Number", phone from suppliers where position("-" in phone)>0;
39 records returned:
Happy Coding!
Other tutorials in this category 1. Using String Functions, Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |