How to set the case sensitivity of a column in design time?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?

Posted 3-Aug-15 10:05am

priyamtheone

Updated 3-Aug-15 10:06am

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 3-Aug-15 18:35pm

Why? This is unnatural constraint.
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ben J. Boyle · Accepted Answer · 2015-08-03T10:20:00

Solution 1

You're probably going to need to use a trigger to change the text to lowercase as there's no way to indicate on a column that you want values stored in lower:

SQL

CREATE TRIGGER TriggerName ON TableName
FOR INSERT, UPDATE
AS
IF UPDATE(ColumnName)
UPDATE TableName SET ColumnName = Lower(ColumnName)

To clarify, using a more real world example for table and column names:

SQL

ALTER TRIGGER trg_lowercaseEmployeeId ON Employee
FOR INSERT, UPDATE
AS
IF UPDATE(employeeId)
BEGIN
	UPDATE e SET e.employeeID=Lower(e.employeeID)
	FROM Employee e  INNER JOIN inserted i ON (i.ID=e.ID)
END

Posted 3-Aug-15 10:20am

Ben J. Boyle

Updated 4-Aug-15 4:23am

v3

Comments

Sergey Alexandrovich Kryukov 3-Aug-15 18:35pm

Inquirer did not mean column name at all.
—SA

Ben J. Boyle 3-Aug-15 18:49pm

That's the only way I can read the question. Without having a schema to work with I jut used generic names for table and column. What's your interpretation? English is an odd language sometimes and maybe I misinterpreted it.

Odd request either way though!

Sergey Alexandrovich Kryukov 3-Aug-15 21:01pm

Yes. Read "to store values in lower case only". It's certainly about data and not about metadata. It's very likely that the inquirer has some big misconceptions, but column names are still irrelevant to the request. I would prefer not answering.
—SA

Ben J. Boyle 4-Aug-15 10:26am

My example wasn't referring to metadata, or at least that wasn't my intent - i just used Tablename and ColumnName because I didn't know the data domain the requester was working in. I've just added a working example based on a table with defined columns, tested on a SQL server database.

Sergey Alexandrovich Kryukov 4-Aug-15 10:55am

You are not getting it. Column name is metadata, and the value of attribute is data.
The class structure taken as data is metadata, but the particular instance and data it carries is not.
—SA

Ben J. Boyle 4-Aug-15 11:15am

"Can it be done through the designer *or by some other means?*"

I'll stop now either way. It's up to the OP if that fits his requirement or not.

priyamtheone 4-Aug-15 12:55pm

@Sergey: I don't have any misconception, because it's my task and I know my task very well. Only I can visualize it properly. It's just that the current requirement is really odd and I never dealt with such a condition. That's why I wanted to get some enhanced view here.

priyamtheone 4-Aug-15 12:44pm

Thanks @Ben! I hope it can't be done through the designer during the table creation as I wanted, and trigger is the safest option. Also opting for the trigger fits better for already existing tables. Otherwise I'll have to go for the whole process manually column by column which is really cumbersome.

I see ambiguity among other posters on your first code example. I hope you too, intended data, and not metadata but gotten misunderstood because of 'TableName' and 'ColumnName' which, more clearly should be read as 'YourTableName' and 'YourColumnName'. Tell me if I got it right.