Dynamic Substrings

The challenge was to extract part of an IP address. Simple enough you'd think given that an IP address has a format of 111.222.333.444 (four blocks of 3 digits, seperated by the ".").

INSTR function takes four values:
INSTR (string1, string2, number, number)

LOCATE function takes three values
LOCATE(expr1, expr2,int)  

Eg IP address: '111.222.333.444'

The problem - remove the last block of numbers:
The desired result

SUBSTRING('111.222.333.444', 1, INSTR ('111.222.333.444', '.', 1,3) -1)

111.222.333

But what if the blocks of numbers are represented by numbers (i.e. no leading zeros), this wil give us an irregular pattern, with only the decimal point as a marker to indicate the break between sections.

It turns out to be fairly simple. Much as on the Oracle database side when using SUBSTR - a negative number works from the end of the string.

SUBSTRING('111.222.333.444' FROM 1 FOR LOCATE('.','111.222.333.444',-1)-1)

Putting a negative in the locate integer parameter makes it work in reverse.