REPLACE & PATINDEX issue

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 29, 2015 at 1:48 pm

  #1801726

  Using the data setup from Eirikur and the data I added, the following works. Not knowing the state of your data, however, I can't promise anything.

  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)

  WHERE

  patindex('%,%', sd.IpAddress) > 0;

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 29, 2015 at 1:48 pm

  #1801727

  TJT (5/29/2015)

  ---

  Wow! Well done!

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

  Fix the data.