January 16, 2015 at 12:10 pm
I have a 3rd party app and db where I can not add constraints or change the data type. They have a phone extension field defines as a varchar(5), and of course a report which only accepts up to 5 numbers. Need to add a rule which will check for this value being either null or any combination up to 5 numbers or cause a raiserror where I can have their app send back a message
January 16, 2015 at 12:25 pm
tcronin 95651 (1/16/2015)
I have a 3rd party app and db where I can not add constraints or change the data type. They have a phone extension field defines as a varchar(5), and of course a report which only accepts up to 5 numbers. Need to add a rule which will check for this value being either null or any combination up to 5 numbers or cause a raiserror where I can have their app send back a message
well the size of the field itself will already raise an error if the application tries to insert more than five chars in a varchar(5) field, so no check costraint is going to help with that. so that field already does exactly what you are asking(allow null or any chars up to five in length)
you could add a constraint to force only letters and numbers to avoid punctuation or whatever, but i don't think that's what you are asking.
if you add something like a constraint that raises an error, the applciation might be supressing the error, the same as what it might be currently doing .
can you explain why you think you need to do this? maybe some peer review can help see the problem differently.
Lowell
January 16, 2015 at 12:28 pm
brainfreeze over remembered syntax
CREATE RULE phonext_rule
AS
@value LIKE '[0-9]'
terrible getting old
January 16, 2015 at 12:29 pm
oh i missed combination of NUMBERS.
rules are being deprecated, you should just create a check constriant.
I know you said you can't add constraints, but technically a rule is a kind of constraint anyway.
Lowell
January 16, 2015 at 12:42 pm
luckily or sadly depending how you look at it we will be lucky to get to 2012 this year, bigger problem with their custom code is to try to get error message from rule violation to raiserror something back on the screen they makes sense to the users
January 16, 2015 at 1:07 pm
Found out cant use rule and message with their app, looks like constraint syntax is a little different if I want to allow any numeric values up to 5
ALTER TABLE dbo.person_test2 ADD CONSTRAINT CK_ext
check (Pext like '[0-9]')
This does not work like the rule if more than a number entered
ALTER TABLE dbo.person_test2 ADD CONSTRAINT CK_ext
check (Pext like '[0-9],[0-9],[0-9],[0-9],[0-9]')
This only allows 5 numbers, how do I get only numbers but up to only 5 values?
January 16, 2015 at 3:05 pm
i think you want a constraint like this:
this is what you want to prevent in the future: anything that is NOT numbers
select p.Pext,* from person_test2 p where p.Pext like '%[^0-9]%'
so all your "good" data looks like this:
select p.Pext,* from person_test2 p where p.Pext NOT like '%[^0-9]%'
so your constraint is like this:
ALTER TABLE dbo.person_test2 ADD CONSTRAINT CK_ext
check (Pext not like '%[^0-9]%')
Lowell
January 16, 2015 at 3:10 pm
thanks that did it found what you had given me in an old substring sample I found
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply