|
Custom Search
| |
|
MySQL SUBSTR() function with examples SUBSTR() function returns a substring from a string starting from the specified position. SUBSTRING() function and MID() function are synonyms of SUBSTR() function. There are two main forms of using SUBSTR() function. SUBSTR(str, pos, len) In this form, len is an optional argument. If it's omitted, SUBSTR() returns a substring starting at position pos and returns everything to the end of the string. If len presents, SUBSTR() returns a substring of len characters long from string str, starting at position pos. OR SUBSTR(str FROM pos FOR len) In this form, FOR len is an optional argument. If it's omitted, SUBSTR() function returns a substring from string str starting at position pos. If FOR len presents, SUBSTR returns a substring of len characters long from string str, starting at position pos. Parameter Values
Examples: 1. Query below returns a substring from string Internet, starting at position 1. It extracts from the first character to the end of the string. -- Result: Internet select substr("Internet", 1); -- Result: Internet select substr("Internet" from 1); 2. Query below returns a substring from string Internet, starting at position 3. It extracts all the remaining characters starting from the third character which is t. -- Result: ternet select substr("Internet", 3); -- Result: ternet select substr("Internet" from 3); 3. Query below returns a substring from string Internet, starting at position 0. It returns an empty string because the first position in value str always starts with a positive or negative number. -- Result: empty string select substr("Internet", 0); -- Result: empty string select substr("Internet" from 0); 4. Query below returns a substring from string Internet, starting at position 3 and for 4 characters only. -- Result: tern select substr("Internet", 3, 4); -- Result: tern select substr("Internet" from 3 for 4); 5. Query below returns a substring from string Internet, starting at position -2. It returns the last 2 characters et. In this case, a negative value -2 for pos indicates that the SUBSTR() function extracts from the second last characters to the end of the string. -- Result: et select substr("Internet", -2); -- Result: et select substr("Internet" from -2); 6. Query below returns a substring of 2 characters long from string Internet, starting at position 3 which is obtained by counting from right to left. -- Result: ne select substr("Internet", -3, 2); -- Result: ne select substr("Internet" from -3 for 2);
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 |