How to can I pull the last two octets from an IP address in SQL?

[Seth Schrock]

I'm trying to the last two portions of a devices IP address (which is in a field) and use it as part of a device ID. Right now I'm using the MID function in SQL to get the 9th through the last digit. This works fine for our IP addresses which are like 192.168.0.1 (most of them), but it doesn't work for the IP addresses that are like 12.232.100.100 (a few of them). I would like to be able to have it look for everything after the second period, but I haven't been able to find anything online for that. Is this possible?

Here is the code I'm using now:

SELECT tblDevice.Company, 
tblDevice.Branch, 
tblDevice.ComputerName, 
tblDevice.Department, 
tblDevice.Type, 
tblDevice.Manufacturer, 
tblDevice.Model, 
tblDevice.SerialNumber, 
tblDevice.ExtendedServiceCode, 
tblDevice.PurchaseDate, 
tblDevice.Warranty, 
tblDevice.Vender, 
tblDevice.OperatingSystem, 
tblDevice.OfficeVersion, 
tblDevice.Status, 
tblDevice.UsageHistory, 
tblDevice.Processor, 
tblDevice.RAM, 
tblDevice.HDD, 
tblDevice.HDDType, 
tblDevice.HDDRemoved, 
(SELECT TOP 1 IPAddress FROM tblIPAddress WHERE DeviceID = SerialNumber) AS IPAddress, 
Year(PurchaseDate) & Month(PurchaseDate) & ('-'+SerialNumber) & ('-'+Mid(IPAddress,9)) AS DeviceID
FROM tblDevice;
 

[Rabbit]

Use the InStr function to find the location of the first dot. Use that plus 1 as the start parameter for another InStr function to find the second dot. Now that you know where the second dot is, you can use the Mid function to grab everything after that point.

[Seth Schrock]

Thank-you Rabbit. That was exactly what I needed. For anyone else viewing this, I replaced line 23 from my OP to be:

Year(PurchaseDate) & Month(PurchaseDate) & ('-'+SerialNumber) & ('-'+Mid(IPAddress,InStr(InStr(IPAddress,".")+1,IPAddress,".")+1)) AS DeviceID