1

Is there a way to tell SQL Server 2012 to store both A and a as A (or both A and a as a) for some given column such that every insert statement does not need to have UPPER() in it?

12
  • 1
    Is the column case sensitive? If it isn't, you shouldn't need UPPER() anywhere. If it is, no, you would need to use a trigger (to just fix it) or a check constraint (to raise an error). Feb 18, 2015 at 20:59
  • 3
    ... and if it's just a "presentation" thing then i'd handle it in the "presentation" layer.
    – swasheck
    Feb 18, 2015 at 21:02
  • If your data's collation is accent insensitive, then you should get what you want. (Usually, because there are characters that look like the same character to you and me, but not to other persons.)
    – RLF
    Feb 18, 2015 at 21:08
  • Maybe a computed column would be an option. Feb 18, 2015 at 21:09
  • @ZabadakGalorex Then you'd need two columns. Feb 18, 2015 at 21:11

2 Answers 2

4

You can do this silently using an INSTEAD OF trigger:

CREATE TRIGGER dbo.tr_table_name
  ON dbo.table_name
  INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.table_name(col1, col2, col_you_care_about)
    SELECT col1, col2, UPPER(col_you_care_about)
    FROM inserted;
END
GO

If you want to raise an error, you can use a CHECK constraint:

CREATE TABLE dbo.blart(a VARCHAR(32));

ALTER TABLE dbo.blart ADD CONSTRAINT ck_col
  CHECK (CONVERT(VARBINARY(32), a) = CONVERT(VARBINARY(32),UPPER(a)));
GO

INSERT dbo.blart(a) VALUES('A'); -- succeeds
GO

INSERT dbo.blart(a) VALUES('a'); -- fails

Result:

Msg 547, Level 16, State 0
The INSERT statement conflicted with the CHECK constraint "ck_col". The conflict occurred in database "dba", table "dbo.blart", column 'a'.

But I have to agree with @swasheck above - what difference does it make how it's stored? If you just want it displayed in upper case, do that at rendering time, not storage time.

1
  • Or have a computed column for it that shows the UPPER/LOWER version, and read that instead of the source column.
    – Andriy M
    May 14, 2015 at 8:35
0

Just to add, in order to emphasise a really important data management point - any aesthetic qualities of your data should be handled at the presentation/reporting/glass level of your IT application - don't let SQL do hard work that it simply doesn't need to do!

Another example I see often is including the "£" or "$" symbol in every row of data to show currency values - why, unless there is a need to differentiate the data at that level, would anyone do that? Get the application or report to do it!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.