Performing SQL string manipulations on a single column I recently came across a problem at an interview which I wasn't able to answer. The ask was to write some SQL fetch the first three number(s) after crossing two 2 decimals.
For example:
Input table:
IP address
1.2.3.4.5
11.22.33.44.55
5.6.7.8.9
111.222.333.444
.
.
.

Output table:
IP address
1.2.3
11.22.33
5.6.7
111.222.333
.
.

My initial approach was to use the inbuilt substring. For example: select substring(ip,0,5) from table. But this is a broken logic as it does not account for values such as 11.22.33 or 111.222.333
How can this be achieved?
use SUBSTRING_INDEX


CREATE TABLE Table1
    (`IP_address` varchar(15))
;
    
INSERT INTO Table1
    (`IP_address`)
VALUES
    ('1.2.3.4.5'),
    ('11.22.33.44.55'),
    ('5.6.7.8.9'),
    ('111.222.333.444')
;




SELECT SUBSTRING_INDEX(`IP_address`,'.',3) FROM Table1;


| SUBSTRING_INDEX(`IP_address`,'.',3) |
| :---------------------------------- |
| 1.2.3                               |
| 11.22.33                            |
| 5.6.7                               |
| 111.222.333                         |


db&lt;&gt;fiddle here