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

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]

• RegularExpressions on SQL-Server:
  http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
• How to integrate .NET Assemblies in SQL-Server: http://www.codeproject.com/KB/database/DotnetAssembliesInMSSQL.aspx?display=Mobile

Solution #2:

A little crude but could this work for you:

SELECT ColumnName FROM TableName WHERE ColumnName LIKE '%.%.%.%'

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 '%'.