Automatically change LOWER to UPPER

Question

Automatically change LOWER to UPPER

karthik M

One Orange Chip

Points: 29139
More actions
March 26, 2009 at 3:07 am

#198359

All,
I have to convert lower case letters into upper case automatically. I dont want to force the user to enter UPPER case. whatever they enter simply i need to convert into UPPER and then need to insert into the corresponding table.
Initially i have created one CHECK constraint which will force the user to enter UPPER case only. But the requirement is to change the lower case into UPPER automaticaly, I used trigger.
create table t1
(
eno char(2)
)
drop trigger i_t1
go
create trigger i_t1
on t1
for insert
as
update t1
set eno = upper(inserted.eno)
from inserted
where t1.eno = inserted.eno
insert into t1 values ('aa')
insert into t1 values ('bb')
insert into t1 values ('cc')
insert into t1 values ('dd')
Any suggestions about my code? any other work around is always welcome!
karthik

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Why not do this client-side? It's formatting, after all.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Simon Liddle SSCrazy Points: 2580 More actions · Answer 2

If it has to be done server-side then I would go with the trigger approach as well but remember to handle updates to the column as well if necessary.

arun.sas SSChampion Points: 11831 More actions · Answer 3

HI,

Before inserting into the table you just made the data to upper then insert,

It’s so simple than trigger.

ARUN SAS

Martin_Burton SSCommitted Points: 1932 More actions · Answer 4

Why not use a computed column:

create table #t1

(

eno char(2)

,enoUpper as Upper(eno)

)

Buzz

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

I would do it with an instead of trigger and not with a trigger.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ross McMicken SSCarpal Tunnel Points: 4437 More actions · Answer 6

Change the insert statement to something like INSERT table UPPER(string). we do this in many of the apps we support.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

If you had enforced stored procedures for table access, then you could do it there.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ross McMicken SSCarpal Tunnel Points: 4437 More actions · Answer 8

RBarryYoung (3/29/2009)
If you had enforced stored procedures for table access, then you could do it there.

That's a good point, and is pretty much our philosphy for a variety of reasons ranging from better security to less bandwidth usage across our WAN. It's also much easier to change a single stored procedure on one server than it is to distribute a new executable to 500 desktops. In our environment, a new executable can take months to get through all of the testing and layering process, so we tend to make the client as thin as possible, and put all of the logic in the database.