2

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?

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.