Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 913
  • Last Modified:

capital letter in sql script

dumb question

Is there an update script that I can change all the data to capital letters(caps lock) in a table

it is for legibility

rutger
0
rutgermons
Asked:
rutgermons
  • 7
  • 5
  • 3
1 Solution
 
stevetheskiCommented:
update table
set columnX = UPPER(columnX)
0
 
stevetheskiCommented:
update table
set columnX = UPPER(columnX),
set coumnY = UPPER(columnY)

etc for the table
0
 
stevetheskiCommented:
you can also do this with a trigger if you ALWAYS want the data in a specific table or column UPPERCASE

CREATE TRIGGER [bob] ON [dbo].[tableX]
FOR INSERT
AS
UPDATE tableX
SET columnX= (
SELECT UPPER(columnX)
FROM inserted) -- inserted is a dynamic table of the database
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
rutgermonsAuthor Commented:
give me ten to test
0
 
rutgermonsAuthor Commented:
the trigger ran succesfully ,although when running the update script itself I got an error,the query returned more than one value

the system hung when I tested the trigger

could you assit please if you know why

rutger
0
 
stevetheskiCommented:
rutger,

what exactly is your trigger?

an what exactly is your update?

i dont see why it would hang
or give you an error
0
 
rutgermonsAuthor Commented:
steve

no worries

I will test it tomorow,off to bed now

talk later
0
 
HilaireCommented:
stevetheski, you update statement will fail if the inserted table contains more than 1 record, which will happen every time you run an update statement affecting more than one record.


in this case the subselect "SELECT UPPER(columnX) FROM inserted" wil return several values and the update statement will crash and raise an error

to handle batch updates properly, the code would be something like

CREATE TRIGGER [bob] ON [dbo].[tableX]
FOR INSERT
AS
UPDATE A
SET columnX= UPPER(I.columnX)
FROM tableX A inner join inserted I on A.primaryKey = I.primaryKey

anyway a trigger won't update existing records
here's a simple stored procedure that will update existing records to full caps

create proc dbo.usp_Strings2FullCaps(@tablename sysname) as
declare @sql varchar(4000)
select @sql = coalesce (@sql + ',', 'update [' + @tablename + '] set ') + '[' + column_name + ']=UPPER([' + column_name + '])'
from information_schema.columns
where table_name = @tablename
and data_type in ('char', 'nchar', 'varchar', 'nvarchar')
exec (@sql)
go

now you can run the SP whenever you want, it will put all the string columns to full caps

exec dbo.usp_Strings2FullCaps 'my table name'

0
 
stevetheskiCommented:
Hilaire.

I was anticapiting he update the records first
then put on the INSERT trigger to keep all NEW records as caps

i If that is the case i don't see what would cause an error.

e.g.
if you have  tableX
Column1
--------------
'MixedCaseLetters'
'MixedCaseLetters'
'MixedCaseLetters'
'MixedCaseLetters'


and Update TableX
SET Column1 = UPPER(Column1)

it will execute fine
the results would be

Column1
--------------
MIXEDCASELETTERS
MIXEDCASELETTERS
MIXEDCASELETTERS
MIXEDCASELETTERS


Then i create an insert trigger
as mentioned above and insert a record

insertt onto tablex values('NewRecord')
the table should contain

Column1
--------------
MIXEDCASELETTERS
MIXEDCASELETTERS
MIXEDCASELETTERS
MIXEDCASELETTERS
NEWRECORD

Am i mistaken?

If so what is wrong with my SQL SERVER?
Because it works for me




0
 
HilaireCommented:
When you insert/update (the trigger should handle updates too) several records in a single sql statement

eg
insert in yourtable(<columnslist>) select <columnslist> from anothertable, the INSERTED table contains several records
triggers should always handle batch insert/updates and set-based operations
0
 
HilaireCommented:
>>Because it works for me<<
How weird !!
the trigger code you posted above would yeld

Column1
--------------
NEWRECORD
NEWRECORD
NEWRECORD
NEWRECORD
NEWRECORD

rutgermons did you accept stevetheski's comment as an answer ?
0
 
stevetheskiCommented:
HiLaire

an update trigger will handle insertts and updates however an insert trigger will only handle inserts

Steve
0
 
stevetheskiCommented:
Oh I see what your saying about the trigger should handle updates too
in case we update a record from an upper value to a mixed or lower case
then we should have an Update trigger.

so if i just had an INSERT trigger are you claiming that all my records would get modified?

Steve
0
 
HilaireCommented:
>>so if i just had an INSERT trigger are you claiming that all my records would get modified?<<
definitely

this statement (it's the code you posted above)

UPDATE tableX
SET columnX= (
SELECT UPPER(columnX)
FROM inserted)

will
-- raise an error if more than one record is inserted
-- update the whole table with the last value inserted if only one record is inserted

try it and you'll see
0
 
HilaireCommented:
>>an update trigger will handle insertts and updates however an insert trigger will only handle inserts<<
This statement is just plain wrong.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now