Posted by avode on 04/26/07 02:30

You would use a constraint which forces upper case in the column.
Don't forget to take into account of the column collation.
The next T-SQL script makes the point more clear.

CREATE TABLE #foo_cs(
col CHAR(7) COLLATE Latin1_General_CS_AI);
GO
CREATE TABLE #foo_ci(
col CHAR(7) COLLATE Latin1_General_CI_AI);
GO
ALTER TABLE #foo_cs
ADD CONSTRAINT uppercase_always_cs
CHECK(col = UPPER(col));
GO
ALTER TABLE #foo_ci
ADD CONSTRAINT uppercase_always_ci
CHECK(col COLLATE Latin1_General_CS_AI = UPPER(col));
GO
INSERT INTO #foo_cs(col) VALUES('99l9999');
GO
INSERT INTO #foo_ci(col) VALUES('99l9999');
GO
INSERT INTO #foo_cs(col) VALUES('99L9999');
GO
INSERT INTO #foo_ci(col) VALUES('99L9999');
GO
SELECT col FROM #foo_cs
WHERE col COLLATE Latin1_General_CI_AI = '99l9999';
GO
SELECT col FROM #foo_ci WHERE col = '99l9999';
GO

DROP TAble #foo_cs, #foo_ci;
GO

--
Andrey Odegov
avodeGOV@yandex.ru
(remove GOV to respond)

[Back to original message]