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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy