[Solved] SQL Query to bring back fields that only contain numbers (specifically an IP address)

I have quite a few tables with a field called company that can either have in them:

“Fake Company” or “5.5.5.5”

The numerical address above of course being an IP address.

I’m not sure how I’d go about writing a select query that brings back ONLY rows that have an IP address in them and not just text. Any ideas?

Solution #1:

You could use RegularExpressions on SQL-Server, for example:

INSERT INTO tIP VALUES('Fake Company');
INSERT INTO tIP VALUES('5.5.5.5');
INSERT INTO tIP VALUES('192.168.5.8');
INSERT INTO tIP VALUES('192.168.5.88');

DECLARE @regexPattern varchar(100);
SET @regexPattern='([0-9]{1,3}.|*.){3}([0-9]{1,3}|*){1}';

SELECT * FROM tIP 
WHERE dbo.RegexMatch( tIP.IP, @regexPattern ) = 1

and here is the function:

CREATE FUNCTION [dbo].[RegexMatch](@Input [nvarchar](4000), @Pattern [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [RegexLib].[RegexLib.UserDefinedFunctions].[RegexMatch]
Respondent: Tim Schmelter

Solution #2:

A little crude but could this work for you:

SELECT ColumnName FROM TableName WHERE ColumnName LIKE '%.%.%.%'
Respondent: Barry Kaye

Solution #3:

Another possibility would be to replace the dots and check if the rest is numeric:

SELECT ColumnName 
FROM TableName 
WHERE ISNUMERIC(REPLACE(ColumnName, '.', '')) = 1

It’s not as exact as the RegEx solution, but it’s sufficient for distinguishing IP adresses from company names, and maybe it’s faster than RegEx or LIKE '%'.

Respondent: Christian Specht

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.