[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server 2000 Data Types XNNNN

Posted on 2007-09-29
9
Medium Priority
?
981 Views
Last Modified: 2008-01-09
In sql server 2000 (windows), is it possible to set the data type when creating a new table to have a specific letter followed by four numbers, ie. J1004? It does not need to auto increment.
Example:
CREATE TABLE dbo.Employee  (
      vEmployeeID VARCHAR(5) UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED,
0
Comment
Question by:katiewebster7
  • 3
  • 3
  • 3
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 600 total points
ID: 19985377
a simple check constraint would do that:

CREATE TABLE dbo.Employee  (
      vEmployeeID VARCHAR(5) PRIMARY KEY NONCLUSTERED CHECK (vEmployeeID LIKE 'J[0-9][0-9][0-9][0-9]') ,

 if the letter is not only J, but can be others to, just change the LIKE expression as needed...
0
 

Author Comment

by:katiewebster7
ID: 19985392
that is not what I am looking for...

I am wanting to create a table that will only accept data for that field that begins with a specific letter, say, "J", followed by four numbers...
0
 

Author Comment

by:katiewebster7
ID: 19985395
sorry - I just reread your post - yes, it seems like that would work - however, I am curious if that is truly the only way...
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 18

Assisted Solution

by:Yveau
Yveau earned 400 total points
ID: 19985401
Yes, but not by a data type.
What you are looking for is a check constraint, or because you are still on SQL 2000, a rule:

Rule is the simple one:
create rule YourRule
as
@Input like '[A-Z][0-9][0-9][0-9][0-9]'

Then apply the rule to the column by
EXEC sp_bindrule 'YourRule', 'dbo.Employee'

In SQL 2005, check constraints are preferred over rules:
CREATE TABLE dbo.Employee  (
      vEmployeeID VARCHAR(5) PRIMARY KEY NONCLUSTERED CHECK (vEmployeeID like '[A-Z][0-9][0-9][0-9][0-9]'))

... the data type will be char(5) in your example, noting 'var' about it and the UNIQUEIDENTIFIER is not possible, that is also a data type. You choose char(5) as (the correct) data type already !

Hope this helps ...



0
 
LVL 18

Expert Comment

by:Yveau
ID: 19985408
Ok, only "J" makes:
CREATE TABLE dbo.Employee  (
      vEmployeeID VARCHAR(5) PRIMARY KEY NONCLUSTERED CHECK (vEmployeeID like 'J[0-9][0-9][0-9][0-9]'))

Hope that helps ...

0
 
LVL 18

Expert Comment

by:Yveau
ID: 19985414
... as A3 said ... beating me by ten minutes ... :-)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19985420
>however, I am curious if that is truly the only way...
no, that's indeed not the only way (but the most effective one).

alternative would be a trigger that performs the same "check", and rolls back the transaction and raises an error to information the user (ie application) with the problem.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 19985428
finally, if you only have J as only possible letter, I would NOT store that in the field, but make the field numeric(4), and eventually add a computed field that concatenates the "J" with the numeric field ...
0
 

Author Comment

by:katiewebster7
ID: 19985451
Thanks a bunch - especially to angelIII !
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question