CODEWITHSUNDEEP
mysqlsqlselectuppercase
apak
apak

Reputation: 55

sql : select uppercase columns in database

I need to select columns that end in an uppercase extension.

for example, look at the following table.

id - picture
1 - abc.JPG
2 - def.jpg
3 - 123.jpg
4 - xyz.JPG

the results should give me the rows 1 and 4 because JPG are in uppercase.

can anyone help?

Upvotes: 1

Views: 3499

Answers (4)

Rick James
Rick James

Reputation: 142296

Some REGEXPs:

'[.][[:upper:]]+$' -- just uppercase letters

'[.].*[[:upper:]]' -- at least one uppercase letter

'[.].*[[:lower:]]' -- at least one lowercase letter; AND together with previous to get upper and lower, etc.

If there could be two "." in the filename, then consider using SUBSTRING_INDEX(picture, '.', -1) to isolate the 'extension'.

Upvotes: 1

KreepN
KreepN

Reputation: 8598

As it can not always be assumed that the file extension will be 3 letters you may use the following to get the first chart after the period and compare it to see if it is uppercase:

select * from table where SUBSTRING(picture,CHARINDEX('.',picture) + 1,1) 
   = upper(SUBSTRING(picture,CHARINDEX('.',picture) + 1,1)) collate SQL_Latin1_General_CP1_CS_AS

enter image description here

Upvotes: 0

Paul Morgan
Paul Morgan

Reputation: 32528

Most SQL languages have a UCASE or UPPER function to convert text to uppercase. I'm also taking advantage of the RIGHT function which isn't in all SQL dialects. If your SQL doesn't have a RIGHT function you'll have to futz with SUBSTRING and LENGTH to get the right three characters in picture. 

Select id, picture
  from table
 where UPPER(RIGHT(TRIM(picture),3)) = RIGHT(TRIM(picture),3)

If the same text converted to uppercase is the same as the unconverted text then it is uppercase in the database and will be selected.

Upvotes: 0

RocketDonkey
RocketDonkey

Reputation: 37259

I'm far from an expert, but case sensitivity has hung me up before. Are you able to modify the structure of your table? One thing that may help is changing the collation of the table as follows (SQLFiddle here):

CREATE TABLE pics (id INT, picture VARCHAR(200))
CHARACTER SET latin1 COLLATE latin1_general_cs;
INSERT INTO pics VALUES 
(1, 'abc.JPG'),
(2, 'def.jpg'),
(3, '123.jpg'),
(4, 'xyz.JPG')

The _cs stands for case sensitive, and I believe the default is case insensitive, which makes case-based comparisons a bit trickier. You can then use the following query to get your rows:

SELECT *
FROM pics
WHERE picture REGEXP '\.[[:upper:]+]$'

If you do not have access to your underlying table, you could try the following, which casts the column in a different character set (latin1), and then changes the collation to support case-insensitive comparisons (SQLFiddle here):

SELECT *
FROM pics
WHERE CAST(picture AS CHAR CHARACTER SET latin1) 
      COLLATE latin1_general_cs REGEXP '\.[[:upper:]+]$'

Upvotes: 3

Related Questions