Find IP addresses in SQL Server

Question

I have over 10 million records that contain an IP address in SQL Server. I need to parse out the addresses into a separate column to further analyze them. I have tried using a PATINDEX which works but only covers one pattern for the IP address. The first octet has to be 50, 41, or 107. The other three octets can range from single to triple digits. Here is what I used which catpures 50/41/107.xxx.xxx.xxx:

SELECT  SUBSTRING(Column_1,PATINDEX('%[50|41|107].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9]%',Column_1)-2,14)
FROM    table_1

How can I capture all IP patterns (50.x.xxx.xxx, 50.xx.x.xxx, etc...) in one query?

You probably mean `\d{1,3}` instead of `[0-9][0-9][0-9]` if that supports standard regular expression notation. — tadman, Jan 12 '15 at 17:15
possible duplicate of [Sorting IP addresses in TSQL](http://stackoverflow.com/questions/6640871/sorting-ip-addresses-in-tsql) — LittleBobbyTables - Au Revoir, Jan 12 '15 at 17:20

score 2 · Answer 1 · answered Jan 12 '15 at 17:17

2

You can use PARSENAME:

SELECT *
FROM dbo.YourTable
WHERE PARSENAME(YourColumn,4) = '50'

answered Jan 12 '15 at 17:17

Lamak

69,480
12
108
116

Pரதீப் · Answer 2 · 2015-01-12T17:22:36.670

0

Use Left and Charindex String function. Try this.

select * from table_1 where
left(Column_1,charindex('.',Column_1)-1) in('50','41','107')

edited Jan 12 '15 at 17:22

answered Jan 12 '15 at 17:16

Pரதீப்

91,748
19
131
172

jeffam217 · Answer 3 · 2015-01-12T18:37:47.150

This query will make sure that you're only capturing IP addresses with only 4 octets.

All octets will need to be in the specified range (currently set to 0-255 but can be changed in the where clause) you can also change the BETWEEN a AND b to IN (a, b, ...) and it will only capture those specific numbers.

It also returns the octets in their own columns as integers which would be a good way to store them moving forward in case you ever needed to do analysis on a specific range, then you won't need to parse text in the future.

Note: Because of the use of TRY_CAST this will only work on SQL Server 2012 and

WITH Octet_Position (column_1, position_1, position_2, position_3)
AS
(
    SELECT
        column_1,
        CHARINDEX('.', column_1),
        CHARINDEX('.', column_1, CHARINDEX('.', column_1) + 1),
        CHARINDEX('.', column_1, (CHARINDEX('.', column_1, CHARINDEX('.', column_1) + 1)) + 1)
    FROM Table_1
)
SELECT
    column_1 AS ip_address,
    TRY_CAST(SUBSTRING(column_1, 0, position_1) AS INT) AS octet_1,
    TRY_CAST(SUBSTRING(column_1, position_1 + 1, position_2 - position_1 - 1) AS INT) AS octet_2,
    TRY_CAST(SUBSTRING(column_1, position_2 + 1, position_3 - position_2 -  1) AS INT) AS octet_3,
    TRY_CAST(SUBSTRING(column_1, position_3 + 1, LEN(column_1) - position_3) AS INT) AS octet_4
FROM Octet_Position
WHERE
    --make sure there are three .'s
    position_1 > 0 AND
    position_2 > 0 AND
    position_3 > 0 AND

    --Make sure the octets are numbers
    TRY_CAST(NULLIF(SUBSTRING(column_1, 0, position_1), '') AS INT) IS NOT NULL AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_1 + 1, position_2 - position_1 - 1), '') AS INT) IS NOT NULL AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_2 + 1, position_3 - position_2 -  1), '') AS INT) IS NOT NULL AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_3 + 1, LEN(column_1) - position_3), '') AS INT) IS NOT NULL AND

    --make sure the octects are in the correct range
    TRY_CAST(NULLIF(SUBSTRING(column_1, 0, position_1), '') AS INT) BETWEEN 0 AND 255 AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_1 + 1, position_2 - position_1 - 1), '') AS INT) BETWEEN 0 AND 255 AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_2 + 1, position_3 - position_2 -  1), '') AS INT) BETWEEN 0 AND 255 AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_3 + 1, LEN(column_1) - position_3), '') AS INT) BETWEEN 0 AND 255

Vadim Volodko · Answer 4 · 2023-04-27T11:03:49.210

--declare @ip nvarchar(15) = '1.2.3.4'
--declare @ip nvarchar(15) = '333.2.3.4'
declare @ip nvarchar(15) = '50.2.3.4'

select @ip, 
       iif (   ( patindex('[0-9]%.[0-9]%.[0-9]%.[0-9]%', @ip) = 0)
            or ( patindex('%[^0-9.]%', @ip) > 0 )
            or ( len(@ip) - len(replace(@ip, '.', '')) <> 3 )
            or ( parsename(@ip, 4) not between 0 and 255 )
            or ( parsename(@ip, 3) not between 0 and 255 )
            or ( parsename(@ip, 2) not between 0 and 255 )
            or ( parsename(@ip, 1) <> 50 )
           , 'bad'
           , 'ok'
         )

Find IP addresses in SQL Server

4 Answers4