ansaurus

Question

Oracle - Select where field has lowercase characters

Answer 1

+16 A:

How about this:

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

BQ 2008-11-25 14:53:00

Answer 2

+1 A:

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.

BrianH 2008-11-25 15:12:44

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.

Justin Cave 2008-11-25 15:15:01

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 2008-11-25 15:21:02

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.

BQ 2008-11-25 15:31:20

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.

BrianH 2008-11-25 15:57:59

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 http://stackoverflow.com/questions/291166/should-i-store-all-the-text-in-my-database-in-uppercase-or-in-lowercase#291217 for more on the functional index.

BQ 2008-11-25 16:10:42

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

David Aldridge 2008-11-25 16:17:55

@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.

BQ 2008-11-25 16:33:10

ansaurus

tags:

views:

answers:

Oracle - Select where field has lowercase characters

related questions