CODEWITHSUNDEEP
sqloracle-databaseselectindexingcase-sensitive
BrianH
BrianH

Reputation: 8241

Oracle - Select where field has lowercase characters

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!

Upvotes: 23

Views: 96710

Answers (6)

Quyen ht
Quyen ht

Reputation: 21

Try this:

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

Upvotes: 1

Sarath Subramanian
Sarath Subramanian

Reputation: 21271

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]');

Upvotes: 1

Dave
Dave

Reputation: 11

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))

Upvotes: -1

Piyush K
Piyush K

Reputation: 11

 SELECT * 
 FROM mytable 
 WHERE FIRST_NAME IN (SELECT FIRST_NAME 
                      FROM MY_TABLE
                      MINUS 
                      SELECT UPPER(FIRST_NAME) 
                      FROM MY_TABLE )

Upvotes: 0

BrianH
BrianH

Reputation: 8241

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.

Upvotes: 2

BQ.
BQ.

Reputation: 9413

How about this:

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

Upvotes: 80

Related Questions