CODEWITHSUNDEEP
mysql
jack
jack

Reputation: 23

mysql replace last group of digits in IP address

I would like to replace the last digits of all IP:port's with a *.

For example: 192.168.1.1:2000 should become 192.168.1.*:2000

Upvotes: 1

Views: 365

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31832

set @ip = '192.168.1.1:2000';
select concat(
    substring_index(substring_index(@ip, ':', 1), '.', 3),
    '.*:',
    substring_index(@ip, ':', -1)
);

If you want to understand how it works run the following query:

set @ip = '192.168.1.1:2000';
select @ip
    , substring_index(@ip, ':', 1)
    , substring_index(@ip, ':', -1)
    , substring_index(substring_index(@ip, ':', 1), '.', 3);

It will return

192.168.1.1  2000  192.168.1

You just need to concatenate the last two columns and the middle part (.*:)

Upvotes: 1

Related Questions