Oracle - Select where field has lowercase characters
I have a table, users, in an Oracle 184.108.40.206 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!
How about this:
select id, first, last from mytable where first != upper(first) or last != upper(last);
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.
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]');
SELECT * FROM mytable WHERE FIRST_NAME IN (SELECT FIRST_NAME FROM MY_TABLE MINUS SELECT UPPER(FIRST_NAME) FROM MY_TABLE )
SELECT * FROM YOU_TABLE WHERE REGEXP_LIKE(COLUMN1,'[a-z]','c'); => Miss, miss lower text SELECT * FROM YOU_TABLE WHERE REGEXP_LIKE(COLUMN1,'[A-Z]','c'); => Miss, MISS upper text
for SQL server where the DB collation setting is Case insensitive use the following:
SELECT * FROM tbl_user WHERE LEFT(username,1) COLLATE Latin1_General_CS_AI <> UPPER(LEFT(username,1))