Convert all data in SQL Server to Upper Case

[sbillori]

I administer SQL Server 7.0 and SQL Server 2000 databases. I've been asked if I can globally convert all text in the databases to UPPER CASE. All data entered after this conversion should also only be UPPER CASE. Does anyone have ideas on how to do this...? Thanks!

[JBane]

There is an UPPER() function available in T-SQL. You can use this to update all the character columns:

Update table
set col1 = UPPPER(col1)

You will have to create a cursor using the information schema views and loop through all the character type columns to determine which to update:

select table_name, column_name from information_schema.columns
where data_type in ('char', 'nchar', 'varchar', 'nvarchar')

Jeff

[shefalin]

Hi,

You could try the following methods:

a. Go and update each text/char/varchar as follows:

Update TableA
set col1 = UPPER(col1)

Need to check system tables to find out which cols. are text.

b. Try BCPing the data into flat files
open using word and convert everything to UPPER.Delete all data and BCP it in again.
In this case you need to disable all the FK keys before deleting the tables.
This is kinda lenghty method, but might work.

syntax for generating script for disabling all FK constrints:

Select 'Alter Table ' + name + ' NOCHECK constraint all '
from sysobjects
where type = 'U'

syntax for generating script for deleting all tables:

Select ' Delete ' + name
from sysobjects
where type = 'U'

syntax for generating script for enabling all FK constrints:

Select 'Alter Table ' + name + ' CHECK constraint all '
from sysobjects
where type = 'U'

[sbillori]

Jeff and Shefali...thanks for the quick response. But how do I set this up so all data entered from here on gets inserted in U-Case only? Thanks again.

[shefalin]

Hi,

For future u need to convert all the text/char/varchar fields to UPPER before inserting
E.g.

Insert tableQ
(Empid,
Empname)
Values
@empid
UPPER(@empname)

OR

You could set it at the application level
E.g.
You could make it a standard that the business layer provide the fields in upper case to the procedures.

[andi_g69]

In this case, why don't you change your front-end or your access layer to use upper case in all display functions?

This will work for existing data as well as for newly inserted data.

If you convert the data to upper case physically there is no chance to roll back to "normal spelling" anymore

[Ads]

Shefali,
You mentioned that the following syntax will disable all FK contraints? does this mean that all FK relations willbe dropped & all the tables can be deleted???

Thanks!

syntax for generating script for disabling all FK constrints:

Select 'Alter Table ' + name + ' NOCHECK constraint all '
from sysobjects
where type = 'U'

syntax for generating script for deleting all tables:

Select ' Delete ' + name
from sysobjects
where type = 'U'

syntax for generating script for enabling all FK constrints:

Select 'Alter Table ' + name + ' CHECK constraint all '
from sysobjects
where type = 'U'

[mist]

I would suggest, like one of the other posters mentioned, that this is much more suited to Application and Presentation layer logic, not the database operation.

If you go through your database like this, then you lose the ability to fall back, however, if you just continue to accept in any case and simply convert it with a one-liner on the way out in your reporting tool then, if you ever decided to go back to mixed case in the future, it's not an issue.

There's also the slight danger that you might miss a field during the conversion process.

Fair enough, if you think that you will *never* see a need for mixed case ever again, go right ahead, but just remember that it will be far more difficult to convert back than it will be to change to upper case.

[mukund]

Hi sbillori,
I would suggest that formatting like Uppercase etc. should be done on the client side or through queries when data is being passed to the front end.
You can achieve a one time conversion to Uppercase by using the Upper function on the char/varchar columns but what about the data that would be created after this process.
Hence I would suggest doing this change on the presentation layer.

-Mukund.