Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-08 : 09:31:19
|
| Brian writes "In an attempt to standardize some web pages, I want to force a field in a table to be uppercase. I would like to be able to have an end user type any case they desire, but have the db always store the text in upper case in that field. Please help. TIA- Brian" |
|
|
andre
Constraint Violating Yak Guru
259 Posts |
Posted - 2002-02-08 : 09:36:39
|
Use the UPPER statement to convert all the letters to upper case.UPDATE tblTable SET Field1=UPPER(@String) |
 |
|
|
net-admin
Starting Member
3 Posts |
Posted - 2002-02-11 : 10:18:12
|
| I was really hoping for a solution where the SQL field converts the data itself. In Access you can set up a field to always use upper or lower case for the characters. Is there someting in SQL that does the same type of thing? A user defined data-type or anything? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-11 : 11:45:09
|
| Actually, the user interface in Access handles that feature, not the database. Similarly, if you're using another application for data entry, you'd have to write it to perform this conversion before you put the data into SQL Server. If that's not an option, you can add an INSERT and UPDATE trigger that modifies the column:CREATE TRIGGER ForceUpper ON myTable FOR INSERT, UPDATE ASUPDATE T SET T.myCol=Upper(T.myCol)FROM myTable T INNER JOIN inserted I ON (T.ID=I.ID)In this case, the ID column represents your primary key. Simply change this to match your primary key column name. |
|
|
|
net-admin
Starting Member
3 Posts |
Posted - 2002-02-11 : 12:16:05
|
I'm going to give that a try. I've got users hitting this table from two locations. One being a web interface (easy to convert to upper case) the other being a link table in Access. The latter being the problem I needed to solve. I'm still pretty green with any advanced MSSQL solutions, so I was easily stumped. I have never set up any triggers, so I plan to stumble through this on a test db and see how far I get. No doubt, I will probably be back with more newbie questions. Thank you for your help.  Edited by - net-admin on 02/11/2002 12:22:11 |
|
|
|
net-admin
Starting Member
3 Posts |
Posted - 2002-02-11 : 12:46:33
|
Thumbs up! That works great!  Are there concerns to using triggers at all? I read somewhere about locking problems. I will be using this trigger on about a dozen fields in this table. Should I set up a trigger for each field, or simply add them to this one trigger? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-11 : 21:17:54
|
| Triggers work on a table level, you don't need to create a trigger for each column. Just make an UPDATE statement that sets all of them to upper:CREATE TRIGGER ForceUpper ON myTable FOR INSERT, UPDATE ASUPDATE T SET T.myCol=Upper(T.myCol), T.col2=Upper(T.col2), T.col3=Upper(T.col3)FROM myTable T WITH (ROWLOCK) INNER JOIN inserted I ON (T.ID=I.ID)Locking could be problematic on this update if there are a lot of rows (1,000+), so I added a ROWLOCK hint to this trigger. It will prevent the whole table from being locked as would normally happen.I think that a lot of, if not all, criticism of trigger overhead is exaggerated; it's no worse than any other badly written piece of code, and just as good as any well written piece of code. This particular example should cause you no problems. However, if you can get the application to do the case conversion you should strive for that. |
|
|
|
|
|
|
|
|
Forcing upper case in a field