April 15, 2002 at 9:25 pm
I am trying to force users to only enter capital letters in a column. I am using a check constraint to do this. Part of the syntax is using the [A-Z]. I was under the understanding that this would only allow capital letters. However this doesn't seem to be the case. How do you enforce ONLY capital letters?
I am using SQL Server 7.0 SP3
Thanks!
April 16, 2002 at 4:55 am
The best way to force (not actuall enforce) captial letters on is to do UPPER(theirvalue) to do it. With checks you have to do something like so.
MYCOL LIKE '[A-Z][A-Z][A-Z][A-Z]'
and that will only work for a 4 charcter column with no spaces allowed. So therefore if it where a 1000 column field you have to build for the whole thing and include the possiblity of spaces and other characters allowed. Or you could build a trigger to loop thru each character and make sure fits. But overall you are better just forcing into UPPER instead.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 19, 2002 at 1:16 pm
Well. I tried and that seems to be evading in my case.
I am using a case insensitive SQLServer installation.
Here is the test case.
create table Test1 (
Username varchar(20) NULL
Constraint UsernameCC1 CHECK (Username = UPPER(Username))
)
go
insert into Test1 values ('abcd')
go
select * from Test1
-- gives me 'abcd' instead of failing.
What has gone wrong?
My DDL? or will it work only for case sensitive installation.
I have not written a trigger because similar kind of check works fine for Oracle.
Any suggesstions?
Thanks,
NeoNash
Edited by - NeoNash on 04/19/2002 1:17:16 PM
April 19, 2002 at 1:33 pm
Since you are case insensitive, the check is correct. 'abc' = 'ABC' in this case.
I've run into this and started working on a char by char check that compares the ASCII values for the characters. Didn't finish it, but that was the only thing I can think of.
Steve Jones
April 19, 2002 at 3:16 pm
Thanks Steve.
There is another question I have raised to better understand this case-sensitive and insensitive issue.
This one example of 'abc'='ABC' is a problem with case-insensitive.
Moreover, if we do select DISTINCT then instead of 'abc' and 'ABC' it gives 'abc' alone.
Anyway, if you find a solution please let me know.
Thanks,
NeoNash
April 19, 2002 at 3:22 pm
Try converting it to varbinary and doing the compare, that's what I use for case sensitive joins.
Andy
April 19, 2002 at 7:43 pm
Thanks Andy.
Could you please give an example.
NeoNash
April 20, 2002 at 6:22 am
convert(varbinary(10), value) = convert(varbinary(10), othervalue)
Andy
April 22, 2002 at 12:21 pm
Wow! That helps.
Thanks Andy.
NeoNash
April 22, 2002 at 2:45 pm
I was able to get the problem fixed. James Travis helped provide the solution. Here is his email:
Sorry, I think maybe I did not make this correctly clear. [A-Z] may not
work unless you are using a case sensitive setup with SQL server. Most
likely you did a case insensitive method and thus it does matter to it. The
way you would use UPPER is in the INSERT or UPDATE query, which if they do
direct editing to the table may require doing somehow thru a trigger.
Example I have a table UpperTest with DDL
CREATE TABLE [UpperTest] (
[pkidx] [int] IDENTITY (1, 1) NOT NULL ,
[meme] [varchar] (5) NOT NULL ,
CONSTRAINT [PK_UpperTest] PRIMARY KEY CLUSTERED
(
[pkidx]
)
)
GO
So here is the trigger
CREATE TRIGGER trMakeUpper ON [dbo].[UpperTest]
FOR INSERT, UPDATE
AS
UPDATE UpperTest SET meme = UPPER(meme) WHERE pkidx IN (SELECT pkidx FROM
inserted)
So when a person enters 1abcd
It will correct to 1ABCD on the insert
Thanks to all for their help/input!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply