How to code to change text to high case for all tables?

Question

How to code to change text to high case for all tables?

adonetok

SSCrazy Eights

Points: 8395
More actions
February 11, 2019 at 1:34 pm

#370542

There are about 50 tables in one database and each table including at lease more than 10 columns.
How to code to change text in all cells to high case for all tables?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

I assume you mean upper case? I'm not familiar with high case.

You would have to write code that does this for each table/column. There isn't a good way. You might be able to script something that finds tables and columns and does an update, but unless this runs more often than once, I might just write the script that does an

UPDATE tablea
set columna = UPPER(columna)
, columnb = UPPER(columnb)

If you want to script this, you could get the tables and columns from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS. Use something like this and when it works correctly, change the select @cmd to exec(@cmd)

DECLARE updatecurs CURSOR FOR SELECT c.TABLE_SCHEMA , c.TABLE_NAME , c.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.DATA_TYPE IN ( 'varchar', 'char', 'nvarchar', 'nchar' ); DECLARE @schema VARCHAR(100) , @table VARCHAR(100) , @col VARCHAR(100) , @cmd VARCHAR(8000); OPEN updatecurs; FETCH NEXT FROM updatecurs INTO @schema , @table , @col; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cmd = 'update ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) SELECT @cmd = @cmd + ' set ' + @col + '= UPPER(' + @col + ')' SELECT @cmd FETCH NEXT FROM updatecurs INTO @schema , @table , @col; END; DEALLOCATE updatecurs;

adonetok SSCrazy Eights Points: 8395 More actions · Answer 2

Yes, I mean upper case. For example, I want to change data "Good" to GOOD".
For test, I created a new database [TEST] and imported all tables which need to update into [TEST].
I ran script from your post but none of cell is updated.
Please help.

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 3

adonetok - Tuesday, February 12, 2019 6:11 AM
Yes, I mean upper case. For example, I want to change data "Good" to GOOD".
For test, I created a new database [TEST] and imported all tables which need to update into [TEST].
I ran script from your post but none of cell is updated.
Please help.

Read the code! Steve's code CREATES the commands. It doesn't EXECUTE them! Modify the code to execute them.

I'd also like to know why you're doing this (whole server case sensitive???) and how you intend to make it so that all future data entered is entered as UPPER CASE only.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

Please don't run code you don't understand without working with it in production. You noted you had a test database, which is slightly better, but what if I had malicious commands that might copy data or do something else? Please check the code.

The code I wrote is for one-time use, not constant running. It is inefficient and problematic. If I needed this to constantly occur, I'm not sure what I'd do. Maybe a trigger, which would be custom code.

If you use my code, you need to change this line
select @cmd
to this
exec(@cmd)

again, I don't recommend this beyond a one time execution.

adonetok SSCrazy Eights Points: 8395 More actions · Answer 5

Thank you, Steve.
I just want to learn.
If I really need, I'll create a temp db and then copy tables into temp db to test.
Question regarding your code below.
Do I need to change all three line "SELECT @cmd" to exec(@cmd)?

BEGIN
SELECT @cmd = 'update ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table)
SELECT @cmd = @cmd + ' set ' + @col + '= UPPER(' + @col + ')'
SELECT @cmd
FETCH NEXT FROM updatecurs
INTO
@schema
, @table
, @col;
END;

adonetok SSCrazy Eights Points: 8395 More actions · Answer 6

Another question.
If I only need to update tables under MyTemp database, how to modify your code?

sgmunson SSC Guru Points: 110639 More actions · Answer 7

adonetok - Thursday, February 14, 2019 8:39 AM
Thank you, Steve.
I just want to learn.
If I really need, I'll create a temp db and then copy tables into temp db to test.
Question regarding your code below.
Do I need to change all three line "SELECT @cmd" to exec(@cmd)?
BEGIN
SELECT @cmd = 'update ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table)
SELECT @cmd = @cmd + ' set ' + @col + '= UPPER(' + @col + ')'
SELECT @cmd
FETCH NEXT FROM updatecurs
INTO
@schema
, @table
, @col;
END;

Only the last one. The first two are setting the value, and the last one is just selecting it, so that's the one to change.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 8

I'd still be really interested in why this evolution is actually necessary and, if necessary, how you're going to force all future inserts and updates to be limited to upper case. Without forcing such things, you're going to have to do this over and over again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 9

Steve M pointed this out, but the

SELECT @CMD + xxxx

lines create a statement. The last one with just

select @cmd

Is the statement you would execute to upper case a particular column. If you copy/paste that into a SSMS window,you can execute it to fix columns values. Do this for a test database and see if that is what you need.

If you change that last line to exec @cmd, it will run the statements instead of printing them in a result set.

HOWEVER, as Jeff mentioned, you don't want to do this over and over. As I mentioned, this does not perform will, will cause log growth, etc. You should not do this more than once without understanding and researching the effects.

adonetok SSCrazy Eights Points: 8395 More actions · Answer 10

Many thanks for all of you.
I did run this script and it works great.
I modified the code to run lower case as below it works great as well.
SELECT @cmd=@cmd+' set '+@col+'= LOWER('+@col+')';
Again, I just want to learn and will use it with extreme caution.
Next, I'll modify code to get Proper Case for selected tables.