Menu
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!
308
We can use the following methods to return the rows that contain uppercase letters. Oracle’s REGEXP_LIKE condition complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. We can therefore use the [:lower:] POSIX character class to check for lowercase characters:
The Oracle LOWER () function converts all letters in a string to lowercase. The following shows the syntax of the Oracle LOWER () function: is the string whose all characters are converted to lowercase. The LOWER () function returns a string which all characters converted to lowercase.
Oracle’s REGEXP_LIKE condition complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. We can therefore use the [:lower:] POSIX character class to check for lowercase characters:
The Oracle LOWER () function converts all letters in a string to lowercase. is the string whose all characters are converted to lowercase. The LOWER () function returns a string which all characters converted to lowercase.
How about this:
select id, first, last from mytable where first != upper(first) or last != upper(last); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
