REPLACE & PATINDEX issue

Question

REPLACE & PATINDEX issue

Viewing 15 posts - 16 through 30 (of 31 total)

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

TJT SSCommitted Points: 1926 More actions · Answer 1

I am trying to get this to work without sample data and I get IP_ADDR is not recognized. I really appreciated everyone's patience on this.

SET NOCOUNT ON;

WITH SAMPLE_DATA(IP_ADDR) AS

( SELECT * FROM SystemsRE AS X(IP_ADDR)

)

SELECT

IP,

MAC

FROM

SAMPLE_DATA sd

CROSS APPLY (SELECT LEFT(IP_ADDR, PATINDEX('%,%', IP_ADDR) - 1)) ca1(IP)

CROSS APPLY (SELECT RIGHT(IP_ADDR, LEN(IP_ADDR) - PATINDEX('%,%', IP_ADDR) - 1)) ca2(MAC);

GO

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

TJT (5/29/2015)
I am trying to get this to work without sample data and I get IP_ADDR is not recognized. I really appreciated everyone's patience on this.
SET NOCOUNT ON;
WITH SAMPLE_DATA(IP_ADDR) AS
( SELECT * FROM SystemsRE AS X(IP_ADDR)
)
SELECT
IP,
MAC
FROM
SAMPLE_DATA sd
CROSS APPLY (SELECT LEFT(IP_ADDR, PATINDEX('%,%', IP_ADDR) - 1)) ca1(IP)
CROSS APPLY (SELECT RIGHT(IP_ADDR, LEN(IP_ADDR) - PATINDEX('%,%', IP_ADDR) - 1)) ca2(MAC);
GO

Quick question, can you post the table structure / create table script for the table SystemsRE ?

😎

TJT SSCommitted Points: 1926 More actions · Answer 3

SystemsRE has IpAddress and SystemID

I am just trying to get the IP Address separated from the MAC address in the IpAddress field.

I tried the example below and get "Invalid length parameter passed to the LEFT or SUBSTRING function."

SET NOCOUNT ON;

WITH SAMPLE_DATA(IP_ADDR) AS

( SELECT IPAddress0 FROM SystemRe AS X

)

SELECT

IP

, MAC

FROM

SAMPLE_DATA sd

CROSS APPLY (SELECT LEFT(IP_ADDR, PATINDEX('%,%', IP_ADDR) - 1)) ca1(IP)

CROSS APPLY (SELECT RIGHT(IP_ADDR, LEN(IP_ADDR) - PATINDEX('%,%', IP_ADDR) - 1)) ca2(MAC);

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

TJT (5/29/2015)
I am trying to get this to work without sample data and I get IP_ADDR is not recognized. I really appreciated everyone's patience on this.
SET NOCOUNT ON;
WITH SAMPLE_DATA(IP_ADDR) AS
( SELECT * FROM SystemsRE AS X(IP_ADDR)
)
SELECT
IP,
MAC
FROM
SAMPLE_DATA sd
CROSS APPLY (SELECT LEFT(IP_ADDR, PATINDEX('%,%', IP_ADDR) - 1)) ca1(IP)
CROSS APPLY (SELECT RIGHT(IP_ADDR, LEN(IP_ADDR) - PATINDEX('%,%', IP_ADDR) - 1)) ca2(MAC);
GO

First, CTE was providing the sample data. To replace the CTE with your table and column names you need something like this:

SELECT

sre.*,

ca1.IP,

ca2.MAC

FROM

dbo.SystemsRE sre

CROSS APPLY (SELECT LEFT(<column name containing IPAddr,MACAddr>, PATINDEX('%,%', <column name containing IPAddr,MACAddr>) - 1)) ca1(IP)

CROSS APPLY (SELECT RIGHT(<column name containing IPAddr,MACAddr>, LEN(<column name containing IPAddr,MACAddr>) - PATINDEX('%,%', <column name containing IPAddr,MACAddr>) - 1)) ca2(MAC);

GO

Replace everything, including the < and >, with your column name from your table.

TJT SSCommitted Points: 1926 More actions · Answer 5

It says Incorrect syntax near '<'

I also noticed it says the LEFT function requires 2 agrument(s)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

TJT (5/29/2015)
It says Incorrect syntax near '<'
I also noticed it says the LEFT function requires 2 agrument(s)

SELECT

sre.*,

ca1.IP,

ca2.MAC

FROM

dbo.SystemsRE sre

CROSS APPLY (SELECT LEFT(sre.YourColumnNameHere, PATINDEX('%,%', sre.YourColumnNameHere) - 1)) ca1(IP)

CROSS APPLY (SELECT RIGHT(sre.YourColumnNameHere, LEN(sre.YourColumnNameHere) - PATINDEX('%,%', sre.YourColumnNameHere) - 1)) ca2(MAC);

GO

TJT SSCommitted Points: 1926 More actions · Answer 7

OK now I get "Invalid length parameter passed to the LEFT or SUBSTRING function"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

TJT (5/29/2015)
OK now I get "Invalid length parameter passed to the LEFT or SUBSTRING function"

Check your data to be sure that there are no null values or values with an IP and no MAC or MAC with no IP.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 9

Another attempt using Lynn's fine code

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.SystemsRE') IS NOT NULL DROP TABLE dbo.SystemsRE;

CREATE TABLE dbo.SystemsRE (SystemID INT NOT NULL, IpAddress VARCHAR(200) NOT NULL);

INSERT INTO dbo.SystemsRE(SystemID,IpAddress)

VALUES

(10001,'132.135.3.100, fe90::bu54:e9z5:61a9:5003')

,(10002,'132.235.219.113, fe80::28d9:l69a:5bbd:gr2f')

;

/* YOUR CODE STARTS HERE */

SELECT

sd.SystemID

,ca1.IP

,ca2.MAC

FROM

dbo.SystemsRE sd

CROSS APPLY (SELECT LEFT(sd.IpAddress, PATINDEX('%,%', sd.IpAddress) - 1)) ca1(IP)

CROSS APPLY (SELECT RIGHT(sd.IpAddress, LEN(sd.IpAddress) - PATINDEX('%,%', sd.IpAddress) - 1)) ca2(MAC);

TJT SSCommitted Points: 1926 More actions · Answer 10

Yes you are correct, there are some nulls. Any way to handle these?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

TJT (5/29/2015)
Yes you are correct, there are some nulls. Any way to handle these?

It isn't nulls. I used the setup provided by Eirikur above and added a null value and got null return values for IP and MAC where the string was nuill. Still looks like a data issue.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

I added the following values to Eirikur's setup and I get failures now. Definitely need to check your data.

,(10003,null)

,(10004,', fe70::28d9:l69a:5bbd:ab2f')

,(10005,'132.235.219.113')

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Further testing, only 10005 causes an error. Check to see if any data is missing the comma (,).

TJT SSCommitted Points: 1926 More actions · Answer 14

OK I will see if there is any missing commas, it may take a while, there is a lot of data.

When I run the query, the results starts to populate and then throws an error

TJT SSCommitted Points: 1926 More actions · Answer 15

Wow! Well done!

Yes I found one entry missing a comma. I don't suppose you have a trick to handle this one?