1

I am using MS SQL Server 2008 and I am trying to extract first 3 octets of an IP address

I tried the solution Extract 1st Three Octets of an IPV4, but SUBSTRING_INDEX is not built-in function name

select SUBSTRING_INDEX(IP_Address, '.',3) as octet, COUNT(*) as C
from ...
where ...

What function can I use?

Community
  • 1
  • 1
Glowie
  • 2,271
  • 21
  • 60
  • 104
  • That's probably a MySQL function, try `SUBSTRING` instead and use it together with `CHARINDEX`. – NickyvV Aug 26 '14 at 20:08
  • 1
    This is not an exact duplicate of a question where the answer is an implementation of `substring_index()`. As @RickS points out, there is a built-in function in SQL Server that does this. – Gordon Linoff Aug 26 '14 at 20:13
  • 5
    Look at [PARSENAME](http://msdn.microsoft.com/en-us/library/ms188006.aspx) – Rick S Aug 26 '14 at 20:21
  • Damn, I was hoping I was the only one who knew about PARSENAME. :) – Tab Alleman Aug 26 '14 at 20:28

1 Answers1

6

This might help you on what to use.

Here's the fiddle although it seems to experience intermittent connection issues at this time: http://www.sqlfiddle.com/#!3/55cda0/15

And basically you can select values by using this code:

SELECT LEFT(IP_Address, LEN(IP_Address) - CHARINDEX('.',REVERSE (IP_Address))) FROM IP

First, it turns around the string, and it will look for the first (which is actually the last, ha!) dot (or whatever separator you set).

After this you just simply select the leftmost of the remaining string where it substracts the location of the separator from the total length; therefore getting rid of anything after the last separator character - which is in your case a dot.

Alex Szabo
  • 3,274
  • 2
  • 18
  • 30