By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,941
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDevice.Company, 
  2. tblDevice.Branch, 
  3. tblDevice.ComputerName, 
  4. tblDevice.Department, 
  5. tblDevice.Type, 
  6. tblDevice.Manufacturer, 
  7. tblDevice.Model, 
  8. tblDevice.SerialNumber, 
  9. tblDevice.ExtendedServiceCode, 
  10. tblDevice.PurchaseDate, 
  11. tblDevice.Warranty, 
  12. tblDevice.Vender, 
  13. tblDevice.OperatingSystem, 
  14. tblDevice.OfficeVersion, 
  15. tblDevice.Status, 
  16. tblDevice.UsageHistory, 
  17. tblDevice.Processor, 
  18. tblDevice.RAM, 
  19. tblDevice.HDD, 
  20. tblDevice.HDDType, 
  21. tblDevice.HDDRemoved, 
  22. (SELECT TOP 1 IPAddress FROM tblIPAddress WHERE DeviceID = SerialNumber) AS IPAddress, 
  23. Year(PurchaseDate) & Month(PurchaseDate) & ('-'+SerialNumber) & ('-'+Mid(IPAddress,9)) AS DeviceID
  24. FROM tblDevice;
  25.  
Jun 29 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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.
Jun 29 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,941
Thank-you Rabbit. That was exactly what I needed. For anyone else viewing this, I replaced line 23 from my OP to be:
Expand|Select|Wrap|Line Numbers
  1. Year(PurchaseDate) & Month(PurchaseDate) & ('-'+SerialNumber) & ('-'+Mid(IPAddress,InStr(InStr(IPAddress,".")+1,IPAddress,".")+1)) AS DeviceID
Jun 29 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.