Validation functions

chm.IsEmail

Returns 1 (true) if given argument is a valid email address, otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsEmail]
(
	@email NVARCHAR(200)
)
RETURNS BIT
		

The regular expression used to validate is a case-insensitive expression as follows:

		  \A(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)\Z
	
		

Example:

		  select chm.IsEmail('name@foo.com')  -- 1
select chm.IsEmail('name@foo')      -- 0
		

chm.IsMobile

Returns 1 (true) if given argument is a valid email address, otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsMobile]
(
	@mobileno NVARCHAR(30)
)
RETURNS BIT
		

The regular expression used to validate is as follows:

		  ^(0|(\+?\d{1,5}))?(-|\s)?\(?\d{3}\)?(-|\s)?\d{3}(-|\s)?\d{4}$
	
		

Example:

		  select chm.IsMobile('9211234567')  -- 1
select chm.IsMobile('09211234567')  -- 1
select chm.IsMobile('921-1234567')  -- 1
select chm.IsMobile('0921-1234567')  -- 1
select chm.IsMobile('+989211234567')  -- 1
select chm.IsMobile('+98-9211234567')  -- 1
select chm.IsMobile('+98-921-1234567')  -- 1
		

chm.IsIPv4

Returns 1 (true) if given argument is a valid IPv4 address, otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsIPv4]
(
	@ip NVARCHAR(20),
	@isMask BIT
)
RETURNS BIT
		

Example:

		  select chm.IsIPv4('::1', 0)  -- 1
select chm.IsIPv4('127.0.0.1', 0)  -- 1
select chm.IsIPv4('192.168.*.*', 0)  -- 0
select chm.IsIPv4('192.168.*.*', 1)  -- 1
		

chm.IsHttpMethod

Returns 1 (true) if given argument is a Http method, otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsHttpMethod]
(
	@method NVARCHAR(10)
)
RETURNS BIT
		

Example:

		  select chm.IsHttpMethod('post')  -- 1
select chm.IsHttpMethod('Post')  -- 1
select chm.IsHttpMethod('POST')  -- 1
select chm.IsHttpMethod('SET')  -- 0
		

chm.IsInteger

Returns 1 (true) if given argument is an integer number, otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsInteger]
(
	@x NVARCHAR(100)
)
RETURNS BIT
		

The regular expression used to validate is as follows:

		  ^(-|\+)?[0-9]+$
	
		

Example:

		  select chm.IsInteger('123')  -- 1
select chm.IsInteger('  123')  -- 1
select chm.IsInteger('+123')  -- 1
select chm.IsInteger('-123')  -- 1
select chm.IsInteger('123.45')  -- 0
		

chm.IsPositiveInteger

Returns 1 (true) if given argument is an integer number, otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsPositiveInteger]
(
	@x NVARCHAR(100)
)
RETURNS BIT
		

The regular expression used to validate is as follows:

		  ^\d+$
	
		

Example:

		  select chm.IsPositiveInteger('123')  -- 1
select chm.IsPositiveInteger('  123')  -- 1
select chm.IsPositiveInteger('+123')  -- 0
select chm.IsPositiveInteger('-123')  -- 0
select chm.IsPositiveInteger('123.45')  -- 0
		

chm.IsMatch

Returns 1 (true) if given argument matches specified regular expression, otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsMatch]
(
	@value      NVARCHAR(MAX),
	@pattern    NVARCHAR(MAX)
)
RETURNS BIT
		

Example:

		  select chm.IsMatch('123', '\d+')  -- 1
select chm.IsMatch('  123 ', '\d+')  -- 1
select chm.IsMatch('  123 ', '^\d+$')  -- 0
		

chm.ListMatch

Returns 1 (true) if all of the items in the given argument match specified regular expression, otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[ListMatch]
(
	@values     NVARCHAR(MAX),
	@pattern    NVARCHAR(MAX),
	@separator  NVARCHAR(MAX)
)
RETURNS BIT
		

Example:

		  select chm.ListMatch('123,0,45', '^\d+$', ',')  -- 1
select chm.ListMatch('123,0,45a', '^\d+$', ',')  -- 0
		

chm.IsJsonObject

Returns 1 (true) if given argument seems to be a json object (it starts with { and ends in } or not), otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsJsonObject]
(
	@x NVARCHAR(MAX)
)
RETURNS BIT
		

Example:

		  select chm.IsJsonObject('{ ... }')  -- 1
select chm.IsJsonObject('{ "a" ')  -- 0
		

chm.IsJsonArray

Returns 1 (true) if given argument seems to be a json array (it starts with [ and ends in ] or not), otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsJsonArray]
(
	@x NVARCHAR(MAX)
)
RETURNS BIT
		

Example:

		  select chm.IsJsonArray('[... ]')  -- 1
select chm.IsJsonArray('[1,2,3')  -- 0
		

chm.IsJson

Returns 1 (true) if given argument seems to be a json value (it is either a json object or a json array), otherwise returns 0 (false).

		  CREATE FUNCTION [chm].[IsJson]
(
	@x NVARCHAR(MAX)
)
RETURNS BIT
		

Example:

		  select chm.IsJson('{}')  -- 1
select chm.IsJson('[]')  -- 1
select chm.IsJson('23')  -- 0