IBM Support

LOCATE_IN_STRING() built-in function

News


Abstract

LOCATE_IN_STRING() built-in function

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > 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:

image-20200115131200-1

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
15 January 2020

UID

ibm11164544