How Do I Allow Particular Letters in a VARCHAR Column and Disallow others

Question

I have a table where figures are entered for certain goods, these figures are used in a calculation. The problem is I need to be able to put NA (Not applicable) in these same columns. I have been able to do it by making the table Varchar  and using CAST to turn this VARCHAR table to Float. My syntax is as follows

SELECT     BID, SUM(CAST(Bikes AS FLOAT)) AS TotalBikes, Year
FROM         dbo.dept
WHERE     (Bikes <> 'NA')
GROUP BY BID,  Year

The Problem is as follows I would like person to enter numbers and only 'NA' how do I prevent them from entering other letters besides NA. The table is already set

THANKS

 

Answer 1

Why not simply make the column nullable, and use NULL to represent "Not applicable"? That is exactly what NULL values are for. You can use coalesce to display N/A when needed:

coaalesce(ltrim(str(Bikes)), 'N/A')

You need to convert the number to a string for presentation, since else you will get an error since varchar has lower data-type precedence than int.

An even better alternative is to put the N/A thing in the presentation layer only.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

You can do it as demonstrated in the following T-SQL example:

USE tempdb;
GO
CREATE TABLE Product (ID INT IDENTITY(1,1) PRIMARY KEY,
  Bikes varchar(64) NOT NULL);
GO

ALTER TABLE dbo.Product ADD CONSTRAINT
	CK_Product CHECK (ISNUMERIC([Bikes]) = 1 OR [Bikes]='NA')
GO

INSERT Product(Bikes) VALUES ('AB')  -- fail
INSERT Product(Bikes) VALUES ('NA')  -- ok
INSERT Product(Bikes) VALUES ('123') -- ok
GO
DROP TABLE tempdb.dbo.Product

You may want to use a different check than ISNUMERIC.

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 3

Thanks I just have to Tweek it to meet my complete requirements but any suggestion on an alternative to ISNUMERIC

Answer 4

The alternative is 

Col NOT LIKE '%[^0-9]%'

This ensures that the field only allows digits. We can also add an extra check for Col LIKE '[1-9]%', so it will disallow 0 as the first digit.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog