Oracle - Select where field has lowercase characters

Question

I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name.

When rows are inserted into this table, the first name and last name fields are supposed to be in all upper case, but somehow some values in these two fields are mixed case.

I want to run a query that will show me all of the rows in the table that have first or last names with lowercase characters in it.

I searched the net and found REGEXP_LIKE, but that must be for newer versions of oracle - it doesn't seem to work for me.

Another thing I tried was to translate "abcde...z" to "$$$$$...$" and then search for a '$' in my field, but there has to be a better way?

Thanks in advance!

Solution 1

How about this:

select id, first, last from mytable
where first != upper(first) or last != upper(last);

Solution 2

I think BQ's SQL and Justin's second SQL will work, because in this scenario:

first_name        last_name
----------        ---------
bob               johnson
Bob               Johnson
BOB               JOHNSON

I want my query to return the first 2 rows.

I just want to make sure that this will be an efficient query though - my table has 500 million rows in it.

When you say upper(first_name) != first_name, is "first_name" always pertaining to the current row that oracle is looking at? I was afraid to use this method at first because I was afraid I would end up joining this table to itself, but they way you both wrote the SQL it appears that the equality check is only operating on a row-by-row basis, which would work for me.

Solution 3

If you are looking for Oracle 10g or higher you can use the below example. Consider that you need to find out the rows where the any of the letter in a column is lowercase.

Column1
.......
MISS
miss
MiSS

In the above example, if you need to find the values miss and MiSS, then you could use the below query

SELECT * FROM YOU_TABLE WHERE REGEXP_LIKE(COLUMN1,'[a-z]');

Comments

[Justin Cave]

The condition is applied on a row by row basis. Of course, your query is going to have to do a full table scan of a 500 million row table in order to process the query, which is going to be slow. If this is a regular occurrence, a function-based index might be helpful.

[BQ.]

Nice point, Justin. I was just going to add the function-based index suggestion as well. Also, if the data is really supposed to be uppercase, you can create a trigger to force this on inserts.

[BQ.]

Brian, not sure what you're planning to do entirely, but do note that you can also run "update mytable set first_name=upper(first_name) and last_name=upper(last_name);" to normalize all your data... you don't need to fetch it row by row and update it outside the database.

[BrianH]

Thanks! These two columns are indexed, so queries are pretty quick against them, but I'll have to investigate a functional index as well. It won't be regular occurrence, I just need it for analysis until we fix it.

[BQ.]

With a normal index, queries should only be quick if they're performed against the specific casing stored in the index. See my comment at stackoverflow.com/questions/291166/… for more on the functional index.

[David Aldridge]

If you were going to run the update then you'd better include predicates to update only the required rows: update mytable set first_name=upper(first_name), last_name=upper(last_name) WHERE upper(last_name) != last_name OR upper(first_name) != first_name

[BQ.]

@David good point. Omitted from my comment above due to length, but that will help prevent some unnecessary IO/locking. Same end result with or without it though.

[Alex Poole]

This doesn't find last_name with the wrong case, and hits the table three times. Why would is be better than the accepted answer?

[Tom Aranda]

Consider expanding your answer. Please explain how this code solves the problem.

[Rubio]

Hmm, I wonder why this isn't working for me on 12c. The query returns all three rows. Could the database NLS settings have an effect?