What is the query to change all the datas to uppercase in a table?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Dear All,

I have a Table named as T1 which has 50 columns.

I need to write a query to change the datas in the 50 columns to uppercase by using a single query.

with expectation,
Viswanathan.M

Posted 9-Apr-11 1:06am

Viswanthan.M.

Updated 9-Apr-11 2:43am

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 9-Apr-11 8:43am

Edited for Readability.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-04-09T01:08:00

Solution 1

Something like:

SQL

UPDATE MyTable
   SET Col1 = UPPER(Col1),
       Col2 = UPPER(Col2), ....

Posted 9-Apr-11 1:08am

Wendelius

Comments

Viswanthan.M. 9-Apr-11 7:16am

Thank You for your reply. but if i have 50 columns,should i write this query for 50 columns?
Is there any other query?
with expectation,
Viswanathan.M

Wendelius 9-Apr-11 7:19am

Don't quite understand your question. You add all the columns in the SET clause. When you have done this you have a single UPDATE statement which will update all the columns to uppercase in a single run.

^That's **Aragon** · Answer 2 · 2011-04-09T01:52:00

Solution 2

SQL

SELECT 'UPDATE YOURTABLENAME SET ' + name + '=' + 'UPPER(' + name + ')'
FROM syscolumns
WHERE id = object_id('YOURTABLENAME')

This will create a query just copy-paste it and run it.

NOTE : Remove identity column if any. Otherwise it will raise an error.

Hope it helps.

Posted 9-Apr-11 1:52am

^That's Aragon

Comments

Dalek Dave 9-Apr-11 8:43am

Good Call.

^That's Aragon 9-Apr-11 8:51am

Thank you DD :)

Costica U · Answer 3 · 2011-04-09T04:30:00

This query will do it.

declare @sql        VARCHAR(8000)
declare @tableName  SYSNAME
set     @tableName  = 'yourTableNameGoesHere'

set     @sql = 'UPDATE ' + @tableName + ' SET ' + 
        (
        select  QUOTENAME(COLUMN_NAME) + ' = UPPER(' + QUOTENAME(COLUMN_NAME) + '), '
        from    INFORMATION_SCHEMA.COLUMNS
        where   TABLE_NAME = @tableName
                AND
                DATA_TYPE IN('char','varchar','nchar','nvarchar')
        order
        by      ORDINAL_POSITION
        for xml path('')
        )
---- remove last ", "
set     @sql = LEFT( @sql, LEN(@SQL)-1)
print @sql
---- uncomment next line to execute the query
--exec (@sql)

DeepikaSrivastava · Answer 4 · 2011-04-09T03:22:00

Solution 3

update table_name set column_name=upper(column_name)

Posted 9-Apr-11 3:22am

DeepikaSrivastava

What is the query to change all the datas to uppercase in a table?

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us