Oracle - Select where field has lowercase characters

sql oracle select indexing case-sensitive
BrianH picture BrianH · Nov 25, 2008 · Viewed 79k times · Source

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!

Answer

BQ. picture BQ. · Nov 25, 2008

How about this:

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

Related questions

DATEDIFF function in Oracle

I need to use Oracle but DATEDIFF function doesn't work in Oracle DB. How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC. SELECT DATEDIFF ('2000-01-01','2000-01-02') AS …

Read More
If statement in select (ORACLE)

Hi I have simply select and works great: select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity, gcrs.Area_name, gcrs.sector_name, substr(gcrs.stream_…

Read More
How to kill a running SELECT statement

How can I stop a running SELECT statement by killing the session? The command is continuously giving me output based on the SELECT statement, I want to stop it in between.

Read More