Limiting a column to certain values

I have a table that contains PLSS information and want to restrict the 
columns to certain values.  Since there is a pattern in what they are 
restricted to, I wonder if there would be an easier way than to create a 
lookup table and use a constraint.

For instance, my values for one field is limited to 3 characters:  from 
01-49, with the third character always an 'E' or 'W'

Would this be easier done in a query or stored procedure or function than to 
create a lookup table?

Thanks for your help. 


0
msnews
4/13/2010 1:57:43 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
1542 Views

Similar Articles

[PageSpeed] 41

In the case you describe you can use a CHECK constraint:

CREATE TABLE Foo (
  keycol INT NOT NULL PRIMARY KEY,
  datacol CHAR(3) CHECK (datacol LIKE '[0-4][1-9][EW]'));

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
4/13/2010 4:27:56 PM
"msnews.microsoft.com" wrote:
> 
> I have a table that contains PLSS information and want to restrict the
> columns to certain values.  Since there is a pattern in what they are
> restricted to, I wonder if there would be an easier way than to create a
> lookup table and use a constraint.
> 
> For instance, my values for one field is limited to 3 characters:  from
> 01-49, with the third character always an 'E' or 'W'
> 
> Would this be easier done in a query or stored procedure or function than to
> create a lookup table?
> 
> Thanks for your help.

A CHECK constraint could be a good move. Assuming that "40E" should be a
valid value too, I would suggest something like this:

  CHECK (datacol LIKE '0[1-9][EW]'  OR  datacol LIKE '[1-4][0-9][EW]')

-- 
Gert-Jan
0
Gert
4/13/2010 4:44:51 PM
Reply:

Similar Artilces:

File Size Limit??
Hi, Can anyone tell me if there is a limit to the file size of an Excel 2000 document. We've got a guy here who has a 63Mb spreadsheet, and keeps wondering why he's getting "Out of memory" messages every time he opens it. I've put an extra 512Mb in his PC, but he's still getting lock-ups. He's convinced that there are no limits to the size of the file, but I'm pretty certain that there is... Anyone help? Thanks, Jon. The limit is memory. -- Don Guillett SalesAid Software donaldb@281.com "Jon" <anonymous@discussions.microsoft.com> ...

Limits
I am doing a fairly easy programme on excel. It is a catchment runoff, and evaporation calculator. But i would like to place a limit on a cell, and have that exess go into another column, is this possible? EG 75 rainfall, soil can hold only 10mm more. SO 10mm INFILTRATES, ie that cell should have a limit of the max soil moisutre, the other 65mm RUNOFF's and thats a different column. ANYBODY PLEASEEEEEEEEEE!!!! lol You need a Workbook_Change event macro for that. The code you would write in that macro should first check that the change occurred in a cell within the range you want. ...

Copy certain rows to new worksheet
Hi everyone I have 5 worksheets in my workbook. In each sheet, column A is left empty for the user to select a number from 1 through 6. This is actually for ranking the rows (1 being best, 2 second best and so on) Ranks 1 to 6 might be all on one sheet or they may all be on different sheets. I would like to have a summary that will copy these rows (1 through 6) onto a separate worksheet, done by macro or vba. Perhaps by looping through each worksheet and doing a copy?? Any help would be appreciated. Thanks, Dave On Sep 15, 6:03=A0am, Dave <Dave.Bea...@lhsc.on.ca> wrote: > Hi ev...

Get column of named cell
I have a named cell - StaffHeader1 - I want to refernce just the Column property of this cell in another piece of vba - how do I do that ? So in my code I am going to do this; iColumn = Column(StaffHeader1) Sheet6.Cells(3, iColumn + 1).Value = iStaffName Any help appreciated Thanks Check your other post. Isis wrote: > > I have a named cell - StaffHeader1 - I want to refernce just the Column > property of this cell in another piece of vba - how do I do that ? > > So in my code I am going to do this; > iColumn = Column(StaffHeader1) > Sheet6.Cells(...

Column width
Is there a way to adjust the column widths in the Account screens? Envo In microsoft.public.money, Envo wrote: >Is there a way to adjust the column widths in the Account screens? It depends what you mean by account screens. Portfolio yes. Registers, no. "Cal Learner-- MVP" <via_newsgroup@please.tnx> wrote in message news:bbop021kqtuktlmju9bv83crvajkflh9ji@4ax.com... > In microsoft.public.money, Envo wrote: > >>Is there a way to adjust the column widths in the Account screens? > > It depends what you mean by account screens. Portfolio yes. > Re...

Picklist values #3
Is there a way to display a chosen picklist value from the lead entity in the account entity when the lead is converted to an account? I don't want to have to maintain two picklists just to view the value after the lead is converted. On Jun 19, 7:12=A0am, Wendy <We...@discussions.microsoft.com> wrote: > Is there a way to display a chosen picklist value from the lead entity in= the > account entity when the lead is converted to an account? =A0I don't want = to > have to maintain two picklists just to view the value after the lead is > converted. There is no such...

CString hex value to CString decimal value
Please bare with me, I am quite new to C++. A CString contains a (large) hexadecimal string value, this value needs to be converted to an decimal value, and then put back as a string into an other CString. Data example: hexadecimal: 93d2f666 = decimal 2480076390 I have googled and found a lot of solutions (hex string to int) but none of them returns the correct decimal value, I suspect that converting a large hex string to int does not work. So if someone could point me to the right direction or could suply a working sample, please do. TIA Alex Your not looking hard enough. ...

Combo Box Values
Access 2007 How do I display a first and last name on the form from a combo box? I would like the address, city, and home phone number to fill in the appropriate fields on the form when selecting the name. You refer to the combo box's Column collection in its AfterUpdate event. Let's assume that the first name is in the second column, the last name is in the third column, the address is in the fourth column, the city is in the fifth column and the phone number is in the sixth column. You'd put their values into text boxes using code like: Private Sub cboSelecti...

Help! IF function is too limited
Hi! I've created a drop-down list of cities in a cell, under which there are two more cells to be filled out with the address and zip codes corresponding to each of the cities. How can I do this, knowing that the original list is a three-column list made of city-address-zip? -- Ringo ------------------------------------------------------------------------ Ringo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27502 View this thread: http://www.excelforum.com/showthread.php?threadid=470178 Use VLOOKUP into the original; list using the DV value =VLOOKUP(B...

2008 R2 dpm 2007 setup: SIS-Limited not found?
I'm trying to run the command line to install the SIS component via ocsetup.exe SIS-Limited /quiet /norestart but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. Any ideas what i need to do to get past this? Thanks It turns out you just need to add the file services role in r2, dont need the manual command.. "markm75g" wrote: > I'm trying to run the command line to install the SIS component via > ocsetup.exe SIS-Limited /quiet /norestart > > but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. ...

Problem using Match on columns of numbers with leading zeroes formatted as text ???
In my test run, I was using a column of first names in workbook A to check and flag their presence in workbook B, The code given to me by Marcus here worked fine. No problem at all. .. When I switched to the real problem by replacing my columns of data with numbers with leading zeroes, the columns being already formatted as text, the program did not find Matches. .. In both workbooks columns of data, cells have an automatic Excel comment that says : "The number in this cell is formatted as text or preceded by an apostrophe". .. What am I missing here ? Is there a spe...

rows and columns in spreadsheet
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is there a way, in Excel, that I can merge cells, rows, or columns, like I can in Numbers? Many thanks for your help. ...

Cannot slect columns in view customization
Hello all: I have a CRM 3.0 installation and I am logged in with the System Administrator role. For some reason when I open a view on an object in customization mode I cannot make any changes to the view columns at all. I am unable to select the columns, when I click on the column name, nothing happens. I have not tried all the objects, but have tried about 10 like leads, accounts, etc. Has anyone seen this behavior before and know how to fix it? Thanks for any and all help. -- John. http://mscrmguy.blogspot.com/ Hi John, Yes, I've seen this before and the solution is to add ...

Q: Rules limit
There is a limit for Exchange Rules on a 32K-size (KB147298) Whether there is a way to change this limit for single users? For me already three users have addressed with such requests :- Thanks Alexander Kenin This is not a configurable limit. It's limited by the size of one RPC packet. "Kenin Alexander" <anonymous@discussions.microsoft.com> wrote in message news:80981636-20EB-4C64-B514-F9BCA635AFE7@microsoft.com... > There is a limit for Exchange Rules on a 32K-size (KB147298). > Whether there is a way to change this limit for single users? > For me already thre...

VALUE Error
I have a large spreedsheet from L2 to BE 1268 The majoority of the cells contain data. It appears that not all the data is NUMERIC as I am getting VALUE errors. I would like a macro that would check all the cells to see which one are NOT numeric so that I could change them to MUNERIC and avoid all the VALUE errors I am getting Going accross from l to be I need 4 different totals( I total col L,M,N,s,t u an other total might total the following col O,P,Q,R etc etc Thanks "pcor" <pcor@discussions.microsoft.com> wrote: > I would like a macro that would check all the cell...

Subtracting Lowest from Highest Value
Hi Everyone. Not sure I can do this or not, but I'll check anyway. My users are entering in three sets of high and low measurements (High1, Low1, High2, Low2, High3, Low3) for a particular part number. I have already set the db up to auto calculate the sum and the average of these six values. Is there any way to "find" the lowest reading and subtract it from the highest reading? Thanks in advance for any assistance. Best Regards, Karen Take a look at the dmax and dmin functions. They should do it for you. "kalyhan" wrote: > Hi Everyone. > > Not ...

Limiting Internet Email for Users
I want to stop users from sending Internet email from my Exchange 2000 server. I use it for a company wide mail server. Can anyone help me stop Internet Email. This might help: http://www.msexchange.org/tutorials/MF009.html -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Bob" <anonymous@discussions.microsoft.com> wrote in message news:14B7AF2C-73E3-4601-84F9-3F7B95043B72@microsoft.com... > I want to stop users from sending Internet email from my Exchange 2000 server. I use it for a company wide mail server. Can anyone help me stop...

Limit outgoing mail limit before send
Hi all Is there any method to avoid to send mail if the mail size is over a preset limit? If I set it in Exchange, the mail will first send to Exchange server and check the size. Any method to check the size before send to server? I got this problem since the connection speed is slow, not like local 100m network. Exchange server and the client Outlook XP PC is far away, connect with a 64K line only. Thanks ...

Addressee limit
How do you check/change the Exchange Server message addressee(To, Cc)limit? When sending messages to servaral recipients, some names are cut out. Thanks. ...

Using Filter with data in two columns
I want to identify rows in my spreadsheet based on criteria in two rows. Example: A B 1 5 6 2 4 2 In the above example, I want to filter the sheet two display the rows for which value in column B minus the value in Column A is a negative value. How could i do this? easiest way is to add a 3rd column and do =B1-A1, then put the filter on. Filter by column C (custom) is less than 0. "Princess V" wrote: > I want to identify rows in my spreadsheet based on criteria in two rows. > > Exa...

Sumif returning incorrect value
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have data in columns c to f and rows 2 to 20 <br> I also have data in a2 to a20. <br> I am trying to sum the data in s2 to f20 when the row matches my criteria in a2 to a 20. The formula that I am using is <br><br>sumif(a2:a20,&quot;=12&quot;,c2:f20) <br><br>the result will only sum the values in column c. <br><br>Does anyone know what the problem is? <br><br>Thanks <br><br>Allan SUMIF() has various limitations. this will work,...

pivot table limitation
Hi, when i tried to generate an excel report. It gives me an error message "Microsoft Excel: Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the PivotTable report, or to the page position. Alternatively, right-click a field, and then click Hide or Hide Levels on the shortcut menu. Error in generating report. Please contact Administrator!" What can i do with this message? Is this the data volumne limitation for pivot table? Thanks for your help You have hit one of Excel's limitations. For pivot tab...

Populate a table from another table column
Is there an easy way to fill a column in one table based on what is entered in another table? For example I have a table for a list of raw materials. I want to automatically add those materials to an order guide table, so that any raw material added to the database is automatically on the order guide table. Any Suggestions? Bo Harris wrote: > Is there an easy way to fill a column in one table based on what is > entered in another table? For example I have a table for a list of > raw materials. I want to automatically add those materials to an > order guide table, so that ...

Configuring a cell to take higher values only
I have made an excel file in which I am writing cumulative warranty report for my product. My subordinates are using this file and I have made it as a shared document. Now I want to avoid any chance of accidental wrong entry in any cell. Is there any method whereby I can configure a cell so that it will take only higher values than the existing entries in the cell. -- Hemant N. Dhuri Mumbai Hi Hemant What you can do is, -select the area in which you want your subordinates to make entries. -Click the menu Data-->Validation -Select settings then --under allow select...

mailbox limit notifications
How can I force on Ecxhange server that notifications of mailbox limit are always sent on english language, not taking care of users language settings in Outlook? If the language setting on the server is set to English, then the notification should be English (per http://support.microsoft.com/default.aspx?scid=KB;EN-US;277856) If that is not the case, you may want to verify that you are running the latest service pack for the OS and Exchange as well as the latest updates. Doug Blanchard [MSFT] Microsoft PSS Please do not send email directly to this alias. This alias is for newsgroup p...