Home

Validate IP address - T-SQL

Taewook Kang

This function will validate an IP address and return a bit value of 0 (false) or 1 (true).

create function [isValidIP] 
(
@ip varchar(15)
) 
	returns bit
as
begin

/******************************************
isValidIP function written by Taewook Kang
http://loudelement.com
*******************************************/

declare @result bit
set @result = 0

if @ip like '%_.%_.%_.%_'
	and @ip not like '%.%.%.%.%'
	and @ip not like '%[^0-9.]%'
	and @ip not like '%[0-9][0-9][0-9][0-9]%'
	and @ip not like '%[3-9][0-9][0-9]%'
	and @ip not like '%2[6-9][0-9]%'
	and @ip not like '%25[6-9]%'
	set @result = 1

return @result

end

Deny non IP strings by adding a constraint.

alter table [table_name_goes_here]
add constraint [check_ip_format]
check (
	[ip] like '%_.%_.%_.%_'
	and [ip] not like '%.%.%.%.%'
	and [ip] not like '%[^0-9.]%'
	and [ip] not like '%[0-9][0-9][0-9][0-9]%'
	and [ip] not like '%[3-9][0-9][0-9]%'
	and [ip] not like '%2[6-9][0-9]%'
	and [ip] not like '%25[6-9]%'
	)