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.
