Performing SQL string manipulations on a single column

Question

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?

score 2 · Accepted Answer · answered Aug 08 '20 at 11:44

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<>fiddle here

Performing SQL string manipulations on a single column

1 Answers1