DB2 – Functions POSSTR and LOCATE

BIG DATA ANALYTICS
BIG DATA ANALYTICS

POSSTR – DB2 Function

Similar to the LOCATE function, but with the arguments reversed.POSSTR returns the position of the first occurrence of the second argument within the first argument. For example

SELECT POSSTR(‘DATABASE ADMINISTRATION’, ‘ADMIN’)
FROM SYSIBM.SYSDUMMY1;

LOCATE – DB2 Function

Returns the position of the first occurrence of the first string the second string. For example

SELECT LOCATE(‘I’, ‘CRAIG MULLINS’)
FROM SYSIBM.SYSDUMMY1;

This SQL statement returns the value 4, because the value ‘I’ first appears in position four within the searched string. It also appears in the 11th position, but that is of no concern to the LOCATE function. Optionally, a third argument can be supplied indicating where the search should start. For example

SELECT LOCATE(‘I’, ‘CRAIG MULLINS’, 7)
FROM SYSIBM.SYSDUMMY1;

This SQL statement returns the value 11, because after position 7, the value ‘I’ first appears in the 11th position. When the third argument is not specified, LOCATE defaults to the beginning of the second string.

Ref here


		
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.