How to find any lower case data/field in all columns of a table (tsql)

user_8733329 0
Viewed 1115

I have a table with about 10 columns and contains about 5000 rows of data. I want to figure out if any field of any column is populated in lower case, then I need to fix it. Because, I need all columns to be in upper case for the ETL process.

I guess if there is a query that turns all alphanumeric columns or any column in upper case should work. But I'm curious to know as well how to find the lower case fields in the whole table for all columns so I know what needs to be changed.

Take this as an example table

Table: Student;

Columns: f_name, l_name, id, address, city, state, zipcode

ssms tsql sql
3 Answers

You can do this query for every column you want, I suggest to do them one by one to avoid any unexpected mistake :

UPDATE table_name SET column_name = UPPER(column_name)
user_87278180

OP mentioned he wanted to see the changes before changing them. This creates a temporary table which displays the changes and then rolls back the UPDATE query. 

BEGIN TRAN

DECLARE @Changes TABLE (
    OLD_f_name VARCHAR(MAX)
    ,NEW_f_name VARCHAR(MAX)
    )

UPDATE Student
SET f_name = UPPER(f_name)
OUTPUT deleted.f_name
    ,inserted.f_name
INTO @Changes(OLD_f_name, NEW_f_name);

SELECT * FROM @Changes

ROLLBACK TRAN

To actually do the update, use COMMIT TRAN instead of ROLLBACK TRAN.

user_12500940

Using UPPER function, you can convert a character expression with lowercase character data converted to uppercase. In this case, below SQL should work.

UPDATE Table SET ColumnName = UPPER (ColumnName)

To check, use below SQL:

SELECT * FROM Table WHERE UPPER(ColumnName) != ColumnName
COLLATE Latin1_General_CS_AS
user_70235030
Related