3

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

Community
  • 1
  • 1
Saihl
  • 31
  • 3
  • 1
    Since you said all columns need to be uppercase, then why not avoid bothering to find which are lower and a) `UPDATE` and `SET` them all to `Column = UPPER(Column)` or b) `SELECT` them all as `UPPER(Column) "Column"`? – justiceorjustus Oct 06 '17 at 15:48
  • 1
    https://stackoverflow.com/questions/858528/sql-query-to-make-all-data-in-a-column-upper-case Check this out, it shows a temporary and permanent solution. – Simon Oct 06 '17 at 15:49
  • 1
    Oh whoops, looks like @justiceorjustus has provided you your solution. – Simon Oct 06 '17 at 15:51
  • 1
    @Simon How dare you use the same basic logic as me! – justiceorjustus Oct 06 '17 at 15:52
  • It wont even pull the lower case alphanumeric field using where column != Upper (column) – Saihl Oct 06 '17 at 15:59
  • @Saihl What collation are you using? – Aaron Dietz Oct 06 '17 at 16:04
  • 1
    "Because, I need all columns to be in upper case for the ETL process." Why? What ETL tool are you using that can't handle this during the ETL process? It seems you are seeking a solution to a problem that should be handled in ETL – Twelfth Oct 06 '17 at 16:07
  • Do this. SELECT * FROM Table WHERE UPPER(ColumnName) != ColumnName – Madhukar Oct 06 '17 at 16:11
  • @Madhukar that doesnt pull information in my table. select f_name from student WHERE f_name collate Latin1_General_CS_AI <> Upper (f_name) That is working. But now I am trying to figure out how to update these values only to upper case – Saihl Oct 06 '17 at 16:14
  • try this. SELECT * FROM Table WHERE UPPER(ColumnName) != ColumnName COLLATE Latin1_General_CS_AS – Madhukar Oct 06 '17 at 16:16

3 Answers3

1

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)
justiceorjustus
  • 2,017
  • 1
  • 19
  • 42
Muayyad Ayesh
  • 122
  • 2
  • it wont work. I think needs collate something somewhere? – Saihl Oct 06 '17 at 15:58
  • 1
    What's the issue you're having? Given your example, it should just be: `update student set f_name = upper(f_name)` – Simon Oct 06 '17 at 16:00
  • @Saihl Do you not have permission or something? This should be a simple fix, as Simon wrote it. – justiceorjustus Oct 06 '17 at 16:01
  • No , i do have permissions to make update. But before i update i even wanna see what it will update. So, if i do select * from student where student_fname <> Upper (Student_fname) it is not showing those values where there is truly students first name in lower case. – Saihl Oct 06 '17 at 16:03
  • Update query is right but pulling the data that needed change has been mentioned by Madhukar and justiceorjustus that works – Saihl Oct 06 '17 at 17:08
1

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.

justiceorjustus
  • 2,017
  • 1
  • 19
  • 42
  • Actually i found this that is honestly bringing back values. Please comment if this solution is wrong. select f_name from student WHERE f_name collate Latin1_General_CS_AI <> Upper (f_name) – Saihl Oct 06 '17 at 16:10
  • Also, this is just an example column. The data i have has alphanumeric data. but wherever it has lower case, i wanna update those. – Saihl Oct 06 '17 at 16:11
  • @Saihl This previews the changes without actually making changes with `ROLLBACK TRAN`. To actually make the changes, use `COMMIT TRAN`. – justiceorjustus Oct 06 '17 at 16:19
0

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
Madhukar
  • 1,194
  • 1
  • 13
  • 29
  • That doesnt pull the data from my table. The values in field is alphanumeric or something else is going on. I had to use this: select f_name from student WHERE f_name collate Latin1_General_CS_AI <> Upper (f_name) – Saihl Oct 06 '17 at 16:17
  • 1
    Perfect, your solution worked for finding the values. Now i will try to update those and see. – Saihl Oct 06 '17 at 16:19
  • Also, i was able to update the right fields. So, thank you! – Saihl Oct 06 '17 at 17:06