Perform String Manipulations

Sometimes, you want to modify the string values that a query returns, such as returning part of a string. For example, if the first name field includes a middle name, such as Edna Martha, you want to return only the middle name, Martha. Use string functions to work with string values, including substring() and strpos() among others.

To extract the portion of a string value after a starting position, use substring(string, startPosition). The startPosition argument starts from 1. In the name Edna Martha, the position of the first letter in Martha is 6. The substring function in this query returns Martha.

However, the position of the middle name isn’t always the same depending on the length of the first name. A better way to get the position of the middle name is to call the strpos() function. Because the first name and middle name are always delimited by a space, you can use strpos() to get the position of the space and then add 1 so it references the start of the middle name. This query combines strpos() and substring() to return the middle name.