CODEWITHSUNDEEP
sqluppercase
MetaGuru
MetaGuru

Reputation: 43843

SQL query to make all data in a column UPPER CASE?

I need a SQL query to make all data in a column UPPER CASE?

Any ideas?

Upvotes: 97

Views: 264511

Answers (2)

KyleMit
KyleMit

Reputation: 29957

If you want to only update on rows that are not currently uppercase (instead of all rows), you'd need to identify the difference using COLLATE like this:

UPDATE MyTable
SET    MyColumn = UPPER(MyColumn)
WHERE  MyColumn != UPPER(MyColumn) COLLATE Latin1_General_CS_AS

A Bit About Collation

Cases sensitivity is based on your collation settings, and is typically case insensitive by default.

Collation can be set at the Server, Database, Column, or Query Level:

-- Server
SELECT SERVERPROPERTY('COLLATION')
-- Database
SELECT name, collation_name FROM sys.databases
-- Column 
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE CHARACTER_SET_NAME IS NOT NULL

Collation Names specify how a string should be encoded and read, for example:

  • Latin1_General_CI_AS → Case Insensitive
  • Latin1_General_CS_AS → Case Sensitive

Upvotes: 19

Tomalak
Tomalak

Reputation: 338228

Permanent:

UPDATE
  MyTable
SET
  MyColumn = UPPER(MyColumn)

Temporary:

SELECT
  UPPER(MyColumn) AS MyColumn
FROM
  MyTable

Upvotes: 227

Related Questions