LOCATE_IN_STRING() built-in function

LOCATE_IN_STRING is a new scalar function in the POSSTR family. LOCATE_IN_STRING has the same capabilities as POSSTR and LOCATE with the addition of an “instance” parameter. The instance parameter is a number that represents which instance of the search-string you are looking for. Until now, if you wanted this sort of functionality you would have to write your own loop and call LOCATE with an updated start position until you found the instance you were looking for. Now it is much simpler.

For complete details, refer to the SQL Reference detail found here: LOCATE_IN_STRING scalar function

Examples:

-- Using the entire string, find the third instance of the string ‘copy’.
VALUES LOCATE_IN_STRING('A copy of a copy of a copy', 'copy', 1, 3)

                         12345678901234567890123456>

Result: 23

-- Extract the quadrants from a TCP/IP IPv4 address
SELECT CLIENT_IP_ADDRESS,
SUBSTRING(CLIENT_IP_ADDRESS, 1, LOCATE_IN_STRING(CLIENT_IP_ADDRESS, '.', 1, 1)-1),
SUBSTRING(CLIENT_IP_ADDRESS, LOCATE_IN_STRING(CLIENT_IP_ADDRESS, '.', 1, 1)+1,
                             LOCATE_IN_STRING(CLIENT_IP_ADDRESS, '.', 1, 2)-
                             LOCATE_IN_STRING(CLIENT_IP_ADDRESS, '.', 1, 1)-1),
SUBSTRING(CLIENT_IP_ADDRESS, LOCATE_IN_STRING(CLIENT_IP_ADDRESS, '.', 1, 2)+1,
                             LOCATE_IN_STRING(CLIENT_IP_ADDRESS, '.', 1, 3)-
                             LOCATE_IN_STRING(CLIENT_IP_ADDRESS, '.', 1, 2)-1),
SUBSTRING(CLIENT_IP_ADDRESS, LOCATE_IN_STRING(CLIENT_IP_ADDRESS, '.', 1, 3)+1)
FROM QSYS2.TCPIP_INFO;

Result: