Replace Statement

Question

Post reply

Replace Statement

TJT

SSCommitted

Points: 1926
More actions
May 19, 2015 at 1:13 pm

#308321

I am having trouble using the replace statement on IP Addresses. I want to replace the ending with ".0" - not matter what the IP Address starts with. Thanks
175.139.45.127
175.139.45.12
175.139.45.1
10.10.10.100
10.10.10.10
10.10.10.1

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

You could do this:

DECLARE @ipaddresses TABLE (ip varchar(30));

INSERT @ipaddresses VALUES

('175.139.45.127'),

('175.139.45.12'),

('175.139.45.1'),

('10.10.10.100'),

('10.10.10.10'),

('10.10.10.1');

SELECT ip = PARSENAME(ip,1)+'.'+PARSENAME(ip,2)+'.'+PARSENAME(ip,3)+'.0'

FROM @ipaddresses

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

These are a few options to do what you're asking for.

SELECT ipaddress,

PARSENAME(ipaddress, 4) + '.' + PARSENAME(ipaddress, 3) + '.' + PARSENAME(ipaddress, 2) + '.0',

CONCAT(PARSENAME(ipaddress, 4), '.', PARSENAME(ipaddress, 3), '.', PARSENAME(ipaddress, 2), '.0'),

LEFT( ipaddress, LEN(ipaddress) - CHARINDEX('.', REVERSE( RTRIM( ipaddress)))) + '.0',

LEFT( ipaddress, CHARINDEX( '.', ipaddress, CHARINDEX( '.', ipaddress, CHARINDEX( '.', ipaddress)+1) + 1)) + '0'

FROM (VALUES

('175.139.45.127'),

('175.139.45.12 '),

('175.139.45.1 '),

('10.10.10.100 '),

('10.10.10.10 '),

('10.10.10.1 '))x(ipaddress)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Alan.B (5/19/2015)
You could do this:
DECLARE @ipaddresses TABLE (ip varchar(30));
INSERT @ipaddresses VALUES
('175.139.45.127'),
('175.139.45.12'),
('175.139.45.1'),
('10.10.10.100'),
('10.10.10.10'),
('10.10.10.1');
SELECT ip = PARSENAME(ip,1)+'.'+PARSENAME(ip,2)+'.'+PARSENAME(ip,3)+'.0'
FROM @ipaddresses

Alan, that's cheating. You didn't test your code. 😛

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TJT SSCommitted Points: 1926 More actions · Answer 4

Sorry, I should have provided more information. The IP Addresses could be any IP addresses.

I was trying to use REPLACE with RIGHT statement

Lowell SSC Guru Points: 323504 More actions · Answer 5

ok here's my verison featuring reverse and charindexes and substrings.

not as pretty, but another alternative:

/*--Results

(No column name)ipaddress

175.139.45.0175.139.45.127

175.139.45.0175.139.45.12

175.139.45.0175.139.45.1

10.10.10.010.10.10.100

10.10.10.010.10.10.10

10.10.10.010.10.10.1

*/

WITH MyCTE (ipaddress)

AS

(

SELECT '175.139.45.127' UNION ALL

SELECT '175.139.45.12' UNION ALL

SELECT '175.139.45.1' UNION ALL

SELECT '10.10.10.100' UNION ALL

SELECT '10.10.10.10' UNION ALL

SELECT '10.10.10.1'

)

SELECT REVERSE('0' + SUBSTRING(REVERSE(ipaddress),CHARINDEX('.',REVERSE(ipaddress)),30)),* FROM MyCTE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

TJT (5/19/2015)
Sorry, I should have provided more information. The IP Addresses could be any IP addresses.
I was trying to use REPLACE with RIGHT statement

The solutions posted will work with any IP address.

You can't use REPLACE with RIGHT. REPLACE will replace all the instances of a string inside another string.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 7

Luis Cazares (5/19/2015)
Alan.B (5/19/2015)
You could do this:
DECLARE @ipaddresses TABLE (ip varchar(30));
INSERT @ipaddresses VALUES
('175.139.45.127'),
('175.139.45.12'),
('175.139.45.1'),
('10.10.10.100'),
('10.10.10.10'),
('10.10.10.1');
SELECT ip = PARSENAME(ip,1)+'.'+PARSENAME(ip,2)+'.'+PARSENAME(ip,3)+'.0'
FROM @ipaddresses
Alan, that's cheating. You didn't test your code. 😛

I guess I didn't :blush:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61136 More actions · Answer 8

TJT (5/19/2015)
Sorry, I should have provided more information. The IP Addresses could be any IP addresses.
I was trying to use REPLACE with RIGHT statement

Ok, by "any IP address" what do you mean exactly?

In the meantime here's a few more solutions, 3 of which use the 2 different splitters referenced in my signature line.

DECLARE @ipaddresses TABLE (ip_id int identity, ip varchar(30));

INSERT @ipaddresses VALUES

('175.139.45.127'),

('175.139.45.12'),

('175.139.45.1'),

('10.10.10.100'),

('10.10.10.10'),

('10.10.10.1');

-- SQL Functions only

-- NOTE: I see that Lowell beat me to it on this solution....

SELECT ip_id, ip = substring(ip,1,len(ip)+1-(charindex('.',reverse(ip))))+'0'

FROM @ipaddresses;

-- Jeff Moden's splitter

SELECT ip_id, ip = s.x+'0'

FROM @ipaddresses i

CROSS APPLY

(

SELECT Item+'.'

FROM dbo.DelimitedSplit8K(i.ip,'.')

WHERE itemNumber < 4

FOR XML PATH('')

) s(x);

-- PatternSplitCM 1

SELECT ip_id, ip =

(

SELECT Item+''

FROM dbo.PatternSplitCM(ip,'%[0-9]%')

WHERE ItemNumber < 7 FOR XML PATH('')

)+'0'

FROM @ipaddresses i;

-- PatternSplitCM 2

SELECT ip_id, ip =

(

SELECT Item+'.'

FROM dbo.PatternSplitCM(ip,'%[.]%')

WHERE ItemNumber < 7 AND [matched] = 0

FOR XML PATH('')

)+'0'

FROM @ipaddresses i;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

TJT SSCommitted Points: 1926 More actions · Answer 9

OK this seems to work:

SELECT IPAddress,

PARSENAME(ipaddress, 4) + '.' + PARSENAME(IPAddress, 3) + '.' + PARSENAME(IPAddress, 2) + '.0' AS SubnetList

FROM (SELECT IPAddress

FROM IPAddresses

)x(IPAddress)

Now each IP address has a unique identifier, but now I'm having trouble adding the unique identifier column

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

You don't need a subquery. I did it like that to have some sample data without creating a table.

SELECT IPAddress,

PARSENAME(ipaddress, 4) + '.' + PARSENAME(IPAddress, 3) + '.' + PARSENAME(IPAddress, 2) + '.0' AS SubnetList

FROM IPAddresses

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TJT SSCommitted Points: 1926 More actions · Answer 11

TJT

SSCommitted

Points: 1926

May 19, 2015 at 3:29 pm

#1799178

Many thanks. I just didn't see it at first.