Add sql constraint on null, empty, or whitespace (C# string.IsNullOrWhiteSpace() equivelant)

Here is a User table. We wanted to make the UserName column not be null, empty, or whitespace.

So the constraint I made is this

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [UserNameNotEmpty] CHECK  (([UserName]<>'' AND rtrim(ltrim(replace(replace(replace([UserName],char((9)),''),char((13)),''),char((10)),'')))<>''))
[UserName]<>''
Empty is checked first and not allowed.
replace([UserName],char((9)),'')
Replaces any instance a Tab character with an empty string.
replace([UserName],char((10)),'')
Replaces any instance a Carriage Return character with an empty string.
replace([UserName],char((13)),'')
Replaces any instance a Line Feed character with an empty string.
ltrim([UserName])
Left trim. It trims spaces from the left side of the string.
rtrim([UserName])
Right trim. It trims spaces from the right side of the string.

Note: You should know that char(9) is tab, char(10) is line feed, char(13) is carriage return.

Here is a complete User table. (This is a legacy system I inherited and I am fixing inadequacies.)

CREATE TABLE [dbo].[User](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](255) NOT NULL,
	[Password] [varchar](255) NOT NULL,
	[Salt] [varchar](255) NOT NULL,
	[FirstName] [varchar](255) NULL,
	[LastName] [varchar](255) NULL,
	[Email] [varchar](255) NULL,
	[Active] [bit] NOT NULL
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Active]  DEFAULT ((1)) FOR [Active]
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [UserNameNotEmpty] CHECK  (([UserName]<>'' AND rtrim(ltrim(replace(replace(replace([UserName],char((9)),''),char((13)),''),char((10)),'')))<>''))
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [UserNameNotEmpty]

Notice, I don’t check null on the constraint, as NOT NULL is part of the UserName column design.

Leave a Reply

How to post code in comments?