|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Default text caseIn the database design - how can you force the input and output in a
particular field to always be in UPPER CASE? re: Field Name: Net Qualifier nvarchar 255 sample output "oz" required "OZ" you can do that only in your select query.
select upper('oz') or select upper(col1) But I would suggest you do it in the front end 2nd question - what is the syntax to convert all entries in a database field
from lower case to upper case? and can I set up a trigger (again need code or syntax sample) to automatically change any new entry to upper case. Show quote "Omnibuzz" wrote: > you can do that only in your select query. > select upper('oz') > or select upper(col1) > > But I would suggest you do it in the front end > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ > > I would really never use a trigger to check for this..
But again,as I told u can do it in the front end.. and may be u can enforce a check constraint in the table definition like this... create table #temp (a varchar(10) check (a collate latin1_general_CS_AS = upper(a)) ) insert into #temp values('asdsd') insert into #temp values('ASASA') drop table #temp Hope this helps. update <table>
set <column> = upper(<column>) You could do it via a trigger, but why would you need to? Isn't it enough to convert the values to uppercase at data retrieval? ML --- http://milambda.blogspot.com/ There are so many points or front ends that impact this data - that is why I
was looking at a possible trigger. This is all inherited stuff and the front ends are mostly proprietary. Show quote "ML" wrote: > update <table> > set <column> = upper(<column>) > > You could do it via a trigger, but why would you need to? Isn't it enough to > convert the values to uppercase at data retrieval? > > ML > > --- > http://milambda.blogspot.com/ SQL Server doesn't handle "input and output." exactly. You can enforce that
only uppercase values are input on the front end (wherever the data is being input), or add UPPER() (or the equivalent in whatever language is being used) to the code that loads the data into SQL Server, if it is not done through a user interface. You can also enforce that the data be uppercase with a check constraint of CHECK (UPPER(thisColumn) = thisColumn). As for output, if you enforce that the data in the table is uppercase, you will be fine. Steve Kass Drew University Show quote "john d" <jo***@discussions.microsoft.com> wrote in message news:5ADF380F-CA4F-4101-A10A-E995D0A18219@microsoft.com... > In the database design - how can you force the input and output in a > particular field to always be in UPPER CASE? > > re: Field Name: Net Qualifier nvarchar 255 > > sample output "oz" required "OZ" Would that check constraint work with a case-insensitive collation?
ML --- http://milambda.blogspot.com/ Oops - no it wouldn't. You can put a COLLATE clause in the constraint.
SK Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:C124D56C-D02B-4F12-AE94-B2602F51CD08@microsoft.com... > Would that check constraint work with a case-insensitive collation? > > > ML > > --- > http://milambda.blogspot.com/ The best choice is that the application uses an appropriate uppercase function on the data BEFORE inserting it into SQL Server. As mentioned by Steve, you can put a CONSTRAINT on the column definition to enforce uppercase -that would force an error back to the application and reject non uppercase input.
If you can restrict data access to stored procedures, then in the stored procedures user the upper() function to present the data as desired. You could have a regularly scheduled (nightly) 'data clean-up' job that ran and did various conversations. And here is a TRIGGER that will change any input into uppercase. NOTE: This is not the recommended way to do this since it will incur a cost for every input operation to the table and is really just a 'kludge' to cover over another inadequacy. CREATE TRIGGER myTrigger ON MyTable AFTER INSERT AS UPDATE MyTable SET UCColumn = upper( UCColumn ) WHERE PKColumn IN ( SELECT PKColumn FROM inserted ) -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Steve Kass" <sk***@drew.edu> wrote in message news:%23e%23%23CMBrGHA.4912@TK2MSFTNGP05.phx.gbl... > SQL Server doesn't handle "input and output." exactly. You can enforce that > only uppercase values are input on the front end (wherever the data is being > input), or add UPPER() (or the equivalent in whatever language is being > used) to the code that loads the data into SQL Server, if it is not done > through a user interface. You can also enforce that the data be uppercase > with a check constraint of CHECK (UPPER(thisColumn) = thisColumn). As for > output, if you enforce that the data in the table is uppercase, you will be > fine. > > Steve Kass > Drew University > > "john d" <jo***@discussions.microsoft.com> wrote in message > news:5ADF380F-CA4F-4101-A10A-E995D0A18219@microsoft.com... >> In the database design - how can you force the input and output in a >> particular field to always be in UPPER CASE? >> >> re: Field Name: Net Qualifier nvarchar 255 >> >> sample output "oz" required "OZ" > >  |
|||||||||||||||||||||||
Other interesting topics