i have a field which is CHAR(20), and it is allowed to only containe number chars.
is there any collation_name can help ? or how can i set the check clause ?
Here is a couple of ways that I would do it (no promises, and there could be better ways :)
DECLARE @.test char(20)
SELECT @.test = '12345678901234567890'
--All 20 characters are numbers
SELECT CASE WHEN @.test LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 'Yes' ELSE 'No' END
--allows trailing blanks (that is what the replace to an ampersand does.)
SELECT CASE WHEN len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(rtrim(@.test),' ','@.'),'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')) = 0 then 1 else 0 end
You can also add a check constraint ot the table
CREATE TABLE TestNumbersOnly (SomeColumn char(20), CONSTRAINT NumericOnlyPlease CHECK (SomeColumn NOT LIKE '%[a-z]%' AND ISNUMERIC(SomeColumn) = 1))
Let's test
--Good
INSERT TestNumbersOnly VALUES ('12345678901234567890')
--Good
INSERT TestNumbersOnly VALUES ('1234567890123456789')
--Bad
INSERT TestNumbersOnly VALUES ('1234567890123456789A')
--Bad
INSERT TestNumbersOnly VALUES ('123(4567890123456789')
--Bad
INSERT TestNumbersOnly VALUES ('123-4567890123456789')
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Why don't you use one of the numeric or integer data types then for the column? It is much cleaner, easier and efficient if you use the correct data type for the data. Using character data types has several issues in that you need to enforce your own constraints, the query optimizer doesn't know that the values are only numeric for example, and if you need to perform arithmetic operations then you need to cast the value explicitly & so on. Is there any business reason to store numeric values in character column? This is a problem with the data model and it is best it to deal it at that level.|||well, the column is actually a one that is the IDs of customers, so i think it is conventionally to set it to CHAR. unfortunately for me, it is also conventionally that the IDs of customers containt only number characters, so the two convention contradict at this point !
after reading your suggestions, i think it is much wise to adopt only one of them, or to let the front-end do the check up
|||No, don't let the front end be the only check. Then it cannot be 100% trusted. Do something on the backend if you have the ability to add constraints.|||yes, that's always a problem! thx for the remainder