July 23, 2009 at 9:22 am
Hello, again
I have another rookie question. I have an inventory table I am trying to constrain.
The database is SQL 2000 with an Access front-end.
The column is called Service_Tag and contains seven Alpha-Numeric characters. These are
the serial numbers for about 2,000 Dell computers. I need to limit data input to ONLY
0-9 and A-H, J-N, and P-Z. In other words, I need to prevent the users from entering the
letters "I" and "O". Serial numbers are like: 12VSE45, 1B334P9.
I have tried the following in Enterprise Manager, right-click on Design Table,
and selecting Properties "Check Constraints"
I can limit the data to Numeric by entering the following:
Service_Tag LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
I have also played around with 'A-H' 'J-N' 'P-Z' but come up with bad syntax errors.
I have searched SSC and Googled. Quite a bit of information but still did not find the proper
syntax for this situation.
Also, in this solution is there any way to not allow punctuation characters (like #$%^&)?
Any help is much appreciated.
Thanks,
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Workstation]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Workstation]
GO
CREATE TABLE [dbo].[Inventory] (
[Workstation_PK] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Service_Tag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Workstation_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Monitor_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Monitor_Service_Tag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Warranty_Expires] [datetime] NULL ,
[Remarks] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vendor_FK] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Department_FK] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Internal_Order_Number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PO_Date] [datetime] NULL ,
[PO_Number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Purchased_By] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Asset_Number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateStamp] [datetime] NULL ,
[TimeStamp] [datetime] NULL ,
[Asset_Family] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Device_Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Building_Location_FK] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sub_Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Changed_By] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Finance_Asset_Number] [numeric](18, 0) NULL ,
[IS_LeaseYorN] [bit] NULL ,
[IS_LoanerYorN] [bit] NULL ,
[Loaner_Expires] [datetime] NULL ,
[Out_Of_Service] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Out_Of_Service_Date] [datetime] NULL ,
[Sent_To_Vendor_Date] [datetime] NULL ,
[Received_From_Vendor_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Workstation] ADD
CONSTRAINT [DF_Workstation_Out_Of_Service_1] DEFAULT (0) FOR [Out_Of_Service],
CONSTRAINT [PK_Workstation] PRIMARY KEY CLUSTERED
(
[Workstation_PK]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_Workstation] UNIQUE NONCLUSTERED
(
[Service_Tag]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
July 23, 2009 at 9:40 am
I created a test table with tag column...
create table test
(
id int identity(1,1),
tag varchar(10) constraint ck_test check ((upper(tag) not like '%[I-O]%') and (tag like '%[0-9]%' or upper(tag) like '%[A-Z]%'))
)
see if this suits you....
July 23, 2009 at 9:42 am
If you want to restrict the number of characters to 7, you may want to include the check for length as well.
create table test
(
id int identity(1,1),
tag varchar(10) constraint ck_test check ((upper(tag) not like '%[I-O]%') and (tag like '%[0-9]%' or upper(tag) like '%[A-Z]%')and len(tag)=7)
)
July 23, 2009 at 10:15 am
Thanks ps for your quick response and script. Since the table already exists and populated, I
just want to open the Enterprise Manager, right-click on Design Table, and select Properties
"Check Constraints. In the Properties area is a box called Constraint Expression. There is where
I need the code to limit the data.
I believe that your code will verify if the data is correct but not prevent users from entering
incorrect data (I may be wrong here).
Looking for something like this:
Service_Tag LIKE '[0-9]'A-H' 'J-N' 'PZ'[0-9]'A-H' 'J-N' 'PZ'
[0-9]'A-H' 'J-N' 'PZ'[0-9]'A-H' 'J-N' 'PZ'[0-9]'A-H' 'J-N' 'PZ'
[0-9]'A-H' 'J-N' 'PZ'[0-9]'A-H' 'J-N' 'PZ''
But, this does not work.
July 23, 2009 at 10:35 am
Service_Tag like
'[0-9A-HJ-NP-Z][0-9A-HJ-NP-Z][0-9A-HJ-NP-Z][0-9A-HJ-NP-Z][0-9A-HJ-NP-Z][0-9A-HJ-NP-Z][0-9A-HJ-NP-Z]'
July 23, 2009 at 11:04 am
Michael - Thank you very much! That's exactly what I was looking for. Works great.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply